java学习笔记——JDBC

1. 什么是JDBC？

使用java程序发送sql语句到数据库服务器端执行，这叫用到了JDBC技术！！！！
jdbc是Oracle-Sun公司设计的一套专门用于java程序操作数据库的接口。

2. 使用jdbc发送sql条件

连接mysql数据库：

• 数据库主机
• 端口
• 数据库用户名
• 数据库密码
• 连接的数据库

3. JDBC的核心API

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

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

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

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

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

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

4. 使用statement接口

连接数据库需要3大条件：用户名，密码，url
这里MySQL使用的url格式是：jdbc:mysql://machine_name:port/dbname

package com.jdbc.b_statement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;//这里使用statement接口进行创建表的操作public class Demo {    private static String password = "root";    private static String user = "root";    private static String url = "jdbc:mysql://localhost:3306/day20";    public static void main(String[] args) {        Statement stmt = null;        Connection con = null;        try {            Class.forName("com.mysql.jdbc.Driver");            con = DriverManager.getConnection(url, user, password);            stmt = con.createStatement();            String sql = "create table student("+                "id int,"+                "name varchar(20)"+                ");";            int count = stmt.executeUpdate(sql);            System.out.println(count);        } catch (Exception e) {            // TODO Auto-generated catch block            e.printStackTrace();            throw new RuntimeException();        }finally {            if (con!=null) {                try {                    con.close();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }            if (stmt!=null) {                try {                    stmt.close();                } catch (SQLException e) {                    // TODO Auto-generated catch block                    e.printStackTrace();                }            }        }    }}

创造一个工具类来简化操作

package com.jdbc.Util;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class JDBCUtil {    private static String password = "root";    private static String user = "root";    private static String url = "jdbc:mysql://localhost:3306/day20";    static{        try {            Class.forName("com.mysql.jdbc.Driver");        } catch (ClassNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    public static Connection getConn(){        try {            return DriverManager.getConnection(url, user, password);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();            throw new RuntimeException();        }    }    public static void getClose(Statement stmt,Connection conn,ResultSet rs){        if (stmt!=null) {            try {                stmt.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if (conn!=null) {            try {                conn.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }        if (rs!=null) {            try {                rs.close();            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }        }    }}

进行DML操作

package com.jdbc.b_statement;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import com.jdbc.Util.JDBCUtil;public class Demo2 {    public static void main(String[] args) {        testInsert();//      testUpdate();//      testDelete();    }    private static void testDelete() {        Connection conn = JDBCUtil.getConn();        try {            Statement stmt = conn.createStatement();            String sql = "DELETE FROM student WHERE id=1";            int count = stmt.executeUpdate(sql);            System.out.println(count);            JDBCUtil.getClose(stmt, conn, null);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    private static void testUpdate() {        Connection conn = JDBCUtil.getConn();        try {            Statement stmt = conn.createStatement();            String sql = "UPDATE student SET name='张学友' WHERE id=1;";            int count = stmt.executeUpdate(sql );            System.out.println(count);            JDBCUtil.getClose(stmt, conn, null);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    private static void testInsert() {        Connection conn = JDBCUtil.getConn();        try {            Statement stmt = conn.createStatement();            String sql = "INSERT INTO student(id,name) VALUES(1,'刘德华');";            int count = stmt.executeUpdate(sql);            System.out.println(count);            JDBCUtil.getClose(stmt, conn, null);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

进行DQL操作

package com.jdbc.b_statement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.jdbc.Util.JDBCUtil;public class Demo3 {    public static void main(String[] args) {        Connection conn = JDBCUtil.getConn();        try {            Statement stmt = conn.createStatement();            String sql = "SELECT * FROM student";            ResultSet rs = stmt.executeQuery(sql);            while (rs.next()) {                System.out.println(rs.getInt("id") + "--"+ rs.getString("name"));            }            JDBCUtil.getClose(stmt, conn, rs);        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }}

5. 用PreparedStatement对象执行SQL操作

package com.jdbc.c_preparedstatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import com.jdbc.Util.JDBCUtil;public class Demo {    public static void main(String[] args) {        testInsert();    }    private static void testInsert() {        //定义连接对象和预编译sql对象        Connection conn  = null;        PreparedStatement stmt = null;        try{            //获取连接            conn = JDBCUtil.getConn();            //PreparedStatement prepareStatement(String sql)            //throws SQLException创建一个 PreparedStatement 对象来将参数化的 SQL 语句发送到数据库。            String sql = "INSERT INTO student VALUES(?,?,?);";//参数化的sql,动态sql            stmt = conn.prepareStatement(sql);//需要一个预编译的sequel语句，将sq发送到数据库端，检查sql语法及用户权限等信息            //给之前参数化的sql语句设置参数            //两个参数：1：是给第几个？设置数据    2：给？设置的数据            stmt.setInt(1, 5);            stmt.setString(2, "黎明");            stmt.setInt(3, 60);            //int executeUpdate()throws SQLException            int count = stmt.executeUpdate();            System.out.println(count);        }catch(Exception e){            e.printStackTrace();        }finally{            //释放资源            JDBCUtil.close(conn, stmt, null);        }    }}

6. CallableStatement对象执行存储过程

需要先在数据库里添加好存储过程，这里只负责执行语句

package com.jdbc.d_callablestatement;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import com.jdbc.Util.JDBCUtil;public class Demo {    public static void main(String[] args) {//      testIn();        testOut();    }    private static void testOut() {        Connection conn = JDBCUtil.getConn();        CallableStatement stmt = null;        ResultSet rs = null;        String sql = "CALL pro_QueryNameById(?,?);";        try {            stmt = conn.prepareCall(sql);            stmt.setInt(1, 2);            stmt.registerOutParameter(2, java.sql.Types.VARCHAR);            rs = stmt.executeQuery();            System.out.println(stmt.getString(2));        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }    }    private static void testIn() {        ResultSet rs = null;        CallableStatement stmt = null;        Connection conn = JDBCUtil.getConn();        String sql = "CALL pro_QueryById(?);";        try {            stmt = conn.prepareCall(sql);            stmt.setInt(1, 2);            rs = stmt.executeQuery();            while (rs.next()) {                System.out.println(rs.getString("name")+"--"+rs.getInt("id"));            }        } catch (SQLException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }finally{            JDBCUtil.getClose(stmt, conn, rs);        }    }}