Java访问数据库知识要点

下载数据库的驱动接口的包 1、加载驱动 Class.forName(driverClassName); 1)Mysql driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/数据库名?useUnicode=true&characterEncoding=gbk

2)SQL Server driverClassName=com.microsoft.jdbc.sqlserver.SQLSeverDriver url=jdbc:sqlserver://localhost:1433;DatabaseName=数据库名 3)Oracle driverClassName=oracle.jdbc.driver.OracleDriver url=jdbc:oracle:thin:@localhost:1521:数据库名 4)access driverClassName=sun.jdbc.odbc.jdbcodbcDriver url=jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=mdb\students.mdb

2、建立与数据库的连接，创建连接对象Connection DriverManager.getConnection(参数列表) 1)Mysql: 参数列表："jdbc:mysql://localhost:3306/数据库名","用户名","密码" 2)SQL Server: 参数列表："jdbc:sqlserver://localhost:1433;DatabaseName=数据库名","用户名","密码" 3)Oracle: 参数列表："jdbc:oracle:thin:@host:端口号:数据库名","用户名","密码" 3、创建Statement对象 4、执行SQL语句，获得结果，可获得结果集ResultSet

例如：选用MySQL作为数据源，操作学生数据库中的学生表 id name sex class date 1 李平 女 一班 2017/9/20 注意：数据库的编码建议和项目编码一致 1）实现连接并查询学生表数据 String driverClassName="com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/Student?useUnicode=true&characterEncoding=gbk"; String userName="root"; String pwd="root"; Class.forName(driverClassName); Connection conn=DriverManager.getConnection(url,userName,pwd); Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); String sql="select * from stu"; ResultSet rs=stmt.executeQuery(sql); while(rs.next()){ System.out.println("姓名："+rs.getString(2)); } rs.close();stmt.close();conn.close(); 2）实现连接并追加学生表数据 String driverClassName="com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/Student?useUnicode=true&characterEncoding=gbk"; String userName="root"; String pwd="root"; Class.forName(driverClassName); Connection conn=DriverManager.getConnection(url,userName,pwd); Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); String sql="insert into stu (id,name,sex,class,date) values(15,李四,男,101,1998-3-4)"; int flag=stmt.executeUpdate(sql); System.out.println(flag); stmt.close();conn.close();

或者使用PreparedStatement(其他操作类似，都可使用PreparedStatement) String driverClassName="com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/Student?useUnicode=true&characterEncoding=gbk"; String userName="root"; String pwd="root"; Class.forName(driverClassName); Connection conn=DriverManager.getConnection(url,userName,pwd); String sql="insert into stu (id,name,sex,class,date) values(?,?,?,?,?)"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1,16); pstmt.setString(2,"张三"); pstmt.setString(3,"女"); pstmt.setString(4,"102"); pstmt.setDate(5,new Date(100,11,11)); int flag=pstmt.executeUpdate(); System.out.println(flag); pstmt.close();conn.close();

3)修改学生表中的数据 String driverClassName="com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/Student?useUnicode=true&characterEncoding=gbk"; String userName="root"; String pwd="root"; Class.forName(driverClassName); Connection conn=DriverManager.getConnection(url,userName,pwd); Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); String sql="update stu set date=1999-2-1 where name=张三"; int flag=stmt.executeUpdate(sql); System.out.println(flag); stmt.close();conn.close();

4)删除学生表中的数据 String driverClassName="com.mysql.jdbc.Driver"; String url="jdbc:mysql://localhost:3306/Student?useUnicode=true&characterEncoding=gbk"; String userName="root"; String pwd="root"; Class.forName(driverClassName); Connection conn=DriverManager.getConnection(url,userName,pwd); Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE); String sql="delete from stu where name like %李%"; int flag=stmt.executeUpdate(sql); System.out.println(flag); stmt.close();conn.close();