1 JDBC入门

1.1 回顾

之前操作mysql数据库：

1）使用mysql客户端工具

2）使用客户端连接mysql服务器

3）发送sql语句到mysql服务器，执行

1.2 什么是JDBC？

使用java程序发送sql语句到数据库服务器端执行，这叫用到了JDBC技术！！！！

dbc是Oracle-Sun公司设计的一套专门用于java程序操作数据库的接口。

1.3 使用jdbc发送sql条件

 连接mysql数据库：

 数据库主机

 端口

 数据库用户名

数据库密码

连接的数据库

              1.4JDBC的核心API

Connection接口：与具体的数据库的连接对象。

                                               StatementcreateStatement()    创建一个静态sql语句对象

                                               PreparedStatementprepareStatement(String sql)  创建预编译的sql语句对象

                                               CallableStatementprepareCall(String sql)   创建存储过程的sql语句对象

Driver接口：数据库驱动程序的接口，所有具体数据库厂商需要的驱动程序需要实现此接口。

                      Connectionconnect(String url, Properties info)  用于获取数据库连接

案例：演示三种连接数据库的方式

在写程序之前，先要导入jar包；

步骤：

1.在project中创建一个文件夹lib

2.把jar包复制放入lib

3.在要导入的jar包点右键，build path --> Configure Build Path

public class DriverDemo { private static String url = "jdbc:mysql://localhost:3306/homework"; private static String user = "root"; private static String password = "root"; public static void main(String[] args) throws Exception { conn1(); conn2(); conn3(); } private static void conn3() throws Exception { //mysql驱动程序的driver实现类对象的静态代码块中已经注册了驱动 //将Driver实现类对象加载到内存中，即可以完成注册 Class.forName("com.mysql.jdbc.Driver"); //获取连接 Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn); } private static void conn2() throws Exception { Driver driver = new com.mysql.jdbc.Driver(); //注册驱动 DriverManager.registerDriver(driver); //获取链接 Connection conn = DriverManager.getConnection(url, user, password); System.out.println(conn); } private static void conn1() throws Exception { //创建一个驱动类对象Driver Driver driver = new com.mysql.jdbc.Driver(); Properties prop = new Properties(); prop.setProperty("user", user); prop.setProperty("password", password); Connection conn = driver.connect(url, prop); System.out.println(conn); } }

                                    

Statement接口：用于执行静态 SQL 语句

                                 nt executeUpdate(String sql)  执行更新操作的sql语句                                                                                                                                                                           （create/alter/drop) DDL语句

public class Demo1 { private static String url = "jdbc:mysql://localhost:3306/homework"; private static String user = "root"; private static String password = "root"; public static void main(String[] args) { Connection conn = null; Statement stmt = null; try { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取连接 conn = DriverManager.getConnection(url, user, password); //创建语句执行者 stmt = conn.createStatement(); String sql = "CREATE TABLE student("+ "id INT,"+ "NAME VARCHAR(20),"+ "age INT"+ ");"; //执行sql语句 int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } }

(insert/update/delete）DML语句

为了使代码更加简洁，我们先写一个JDBCUtil类，供我们使用

public class JDBCUtil { private static String url = "jdbc:mysql://localhost:3306/homework"; private static String user = "root"; private static String password = "root"; static{ try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //获取连接 public static Connection getConn(){ try { Connection conn = DriverManager.getConnection(url, user, password); return conn; } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(); } } //释放资源 public static void close(Connection conn,Statement stmt,ResultSet rs){ if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } } }DML语句演示：

public class Demo2 { public static void main(String[] args) { testInsert(); testUpdate(); testDelete(); } private static void testDelete() { Connection conn = null; Statement stmt = null; try { //获取连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "delete from student where id=1"; //获取语句执行者 stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } } private static void testUpdate() { Connection conn = null; Statement stmt = null; try { //创建连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "update student set name='Hins Chueng' where id=1"; //获取语句执行者 stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); }finally{ //释放资源 JDBCUtil.close(conn, stmt, null); } } private static void testInsert(){ Connection conn = null; Statement stmt = null; //获取连接对象 try { conn = JDBCUtil.getConn(); //定义sql String sql = "insert into student values(1,'Hins',33)"; //获取语句执行者 stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } } }

ResultSet executeQuery(String sql)  执行查询操作的sql语句(select)（DQL查询语句）

演示：

public class Demo3 { public static void main(String[] args) { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //获取连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "select * from student"; //获取语句执行者对象 stmt = conn.createStatement(); rs = stmt.executeQuery(sql); while(rs.next()){ System.out.println(rs.getInt("ID")+"--"+rs.getString("name")+"--"+rs.getInt("age")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, rs); } } }这里executeQuery()的返回值是ResultSet （类似迭代器），通过ResultSet.next()判断是否存在数据，Result.getInt(),或者get其他类型的数据，传入改字段的名称，即可返回该该行对应的查询值；

PreparedStatement接口：用于执行预编译的 SQL语句（是Statement的子接口）

                                               intexecuteUpdate()  执行更新操作的sql语句

                                               ResultSetexecuteQuery()  执行查询操作的sql语句

演示：

public class Demo { public static void main(String[] args) { // testInsert(); // testUpdate(); // testDelete(); testSelect(); } private static void testSelect() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = JDBCUtil.getConn(); String sql = "select * from student where id=?;"; stmt = conn.prepareStatement(sql); stmt.setInt(1, 1); rs = stmt.executeQuery(); while (rs.next()) { System.out.println(rs.getInt("id")+"--"+rs.getString("name")+"--"+rs.getInt("age")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.close(conn, stmt, rs); } } private static void testDelete() { Connection conn = null; PreparedStatement stmt = null; try { //创建连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "delete from student where id=?;"; //创建语句执行者对象 stmt = conn.prepareStatement(sql); stmt.setInt(1, 2); stmt.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.close(conn, stmt, null); } } private static void testUpdate() { Connection conn = null; PreparedStatement stmt = null; try { //创建连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "update student set name=? where id=?"; //创建语句执行者对象 stmt = conn.prepareStatement(sql); stmt.setString(1, "Jacky Chueng"); stmt.setInt(2,2); stmt.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.close(conn, stmt, null); } } private static void testInsert() { Connection conn = null; PreparedStatement stmt = null; try { //创建连接对象 conn = JDBCUtil.getConn(); //定义sql String sql = "insert into student values(?,?,?);"; //创建语句执行者对象 stmt = conn.prepareStatement(sql); stmt.setInt(1, 2); stmt.setString(2, "Hins Chueng"); stmt.setInt(3, 33); stmt.executeUpdate(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.close(conn, stmt, null); } } }

利用数据库完成登陆案例：

public class Login { private static String user = "uu"; private static String password = "123456"; public static void main(String[] args) { //用Statement做 Login1(); //用PreparedStatement做 Login2(); } private static void Login2() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = JDBCUtil.getConn(); String sql = "select * from user where name=? and password=?"; stmt = conn.prepareStatement(sql); stmt.setString(1, user); stmt.setString(2, password); rs = stmt.executeQuery(); if(rs.next()){ System.out.println("登陆成功"); }else{ System.out.println("登陆失败"); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, rs); } } private static void Login1() { Connection conn = null; Statement stmt = null; ResultSet rs = null; try { //创建连接对象 conn = JDBCUtil.getConn(); //设置sql语句 String sql = "select * from user where name='"+user+"' and password='"+password+"'"; //创建语句执行者 stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if(rs.next()){ System.out.println("登陆成功"); }else{ System.out.println("登陆失败"); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtil.close(conn,stmt,rs); } } }

这里使用了PreparedStatement 和 Statement 两种方法来完成这个功能，但是如果使用Statement 验证登陆的话，会出现不安全的情况，就是在修改输入的用户名，在用户名后加入 ' OR 1=1 --  ，就会使代码恒成立，即当user表中有数据时，即使用户名和密码都错误，也可以完成登陆；

CallableStatement接口：用于执行 SQL 存储过程的接口（是PreparedStatement的子接口）

                                               ResultSet  executeQuery()  执行存储过程的sql语句

案例演示：

public class Demo { public static void main(String[] args) { //执行带输入输出参数的存储过程 test(); } private static void test() { // TODO Auto-generated method stub Connection conn = null; CallableStatement stmt = null; ResultSet rs = null; try { conn = JDBCUtil.getConn(); String sql = "call Pro_NameById(?,?);"; stmt = conn.prepareCall(sql); //给问号赋值 stmt.setInt(1, 1); //如果存储过程带有输出参数的时候，首先需要注册，输出参数的类型 stmt.registerOutParameter(2, java.sql.Types.VARCHAR); //发送并执行SQL stmt.executeQuery(); System.out.println(stmt.getString(2)); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ JDBCUtil.close(conn, stmt, rs); } } }

                         ResultSet接口：结果集对象。 存储所有数据库查询的结果，用该对象进行数据遍历。

                                              booleannext() ： 把光标移动到下一行。如果下一行有数据，返回true，如果没有下一行数据，返回false。

                                               getXXX（列索引|列字段名称）： 获取字段的数据

图解：

使用方法类似迭代器，获取数据时时通过getInt getBoolean 等方法获取该字段数据；