java学习(21)--JDBC
上篇写完了关于数据库MySql的内容,这篇开始写JDBC的相关内容。
1.JDBC入门
1.1 什么是JDBC?
使用java程序发送sql语句到数据库服务器端执行,这叫用到了JDBC技术
jdbc是Oracle-Sun公司设计的一套专门用于java程序操作数据库的接口。
1.2 使用jdbc发送sql条件
连接mysql数据库:
(1)数据库主机
(2)端口
(3)数据库用户名
(4)数据库密码
(5)连接的数据库
1.3 JDBC 的核心 API
(1)Driver 接口: 数据库驱动程序的接口,所有具体数据库厂商需要的驱动程序需要实现此接口。Connection connect(String url, Properties info) 用于获取数据库连接
(2)Connection接口:与具体的数据库的连接对象。
Statement createStatement() 创建一个静态sql语句对象
PreparedStatement prepareStatement(String sql) 创建预编译的sql语句对象
CallableStatement prepareCall(String sql) 创建存储过程的sql语句对象
(3)Statement接口:用于执行静态 SQL语句
int executeUpdate(String sql) 执行更新操作的sql语句(create/alter/drop) DDL语句
和(insert/update/delete)DML语句
ResultSet executeQuery(String sql) 执行查询操作的sql语句
(select)(DQL查询语句)
(4)PreparedStatement接口:
用于执行预编译的 SQL语句(是Statement的子接口)
int executeUpdate() 执行更新操作的sql语句
ResultSet executeQuery() 执行查询操作的sql语句
(5)CallableStatement接口:用于执行 SQL存储过程的接口(是PreparedStatement的子 接口)
ResultSet executeQuery() 执行存储过程的sql语句
(6)ResultSet接口:结果集对象。存储所有数据库查询的结果,用该对象进行数据遍历。
boolean next() :把光标移动到下一行。如果下一行有数据,返回true,如果没有下一行数据,返回false。
getXXX(列索引,列字段名称): 获取字段的数据
1.4 连接数据库
方法一(直连):
private static String user = "root"; private static String password = "root"; private static String url = "jdbc:mysql://localhost:3306/testJDBC"; public static void main(String[] args) throws Exception { //创建一个Driver对象 Driver driver = new com.mysql.jdbc.Driver(); //创建一个properties集合 Properties prop = new Properties(); prop.put("user", user); prop.put("password", password); //连接数据库 Connection conn = driver.connect(url, prop); //打印连接对象 System.out.println(conn); }
方法二:
private static String url = "jdbc:mysql://localhost:3306/testJDBC"; private static String user = "root"; private static String password = "root"; //使用驱动管理类,连管理我们的驱动程序,并获取连接 public static void main(String[] args) 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 String url = "jdbc:mysql://localhost:3306/testJDBC"; private static String user = "root"; private static String password = "root"; public static void main(String[] args) throws Exception { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //连接数据库 Connection conn = DriverManager.getConnection(url, user, password); //打印连接对象 System.out.println(conn); }
2.Statement对象执行SQL操作
2.1 执行DDL操作
分析:通过Statement对象执行sql操作,创建一张student表:
public class Test01 { private static String url = "jdbc:mysql://localhost:3306/testJDBC"; 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(); //定义sql语句 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(); //throw new RuntimeException(); }finally{ //释放资源 if (conn!=null) { try { conn.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(); } } } } }
2.2 执行DML操作
//删除操作 private static void testDelete() { Connection conn = null; Statement stmt = null; try { conn = JDBCUtil.getConn(); String sql = "delete from student where id=1;"; stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, null); } } //修改操作 private static void testUpdate() { Connection conn = null; Statement stmt = null; try { conn = JDBCUtil.getConn(); String sql = "update student set name='s2' where id=1;"; stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, null); } } //插入操作 private static void testInsert() { Connection conn = null; Statement stmt = null; try { conn = JDBCUtil.getConn(); String sql = "INSERT INTO student VALUES(1,'s1',12);"; stmt = conn.createStatement(); int count = stmt.executeUpdate(sql); System.out.println(count); } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, null); } }
2.3 执行DQL查询操作
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(); //执行DQL查询语句 rs = stmt.executeQuery(sql); while (rs.next()) { System.out.println(rs.getInt("ID")+"--"+rs.getString("NAME")); } }catch(Exception e){ e.printStackTrace(); }finally{ JDBCUtil.close(conn, stmt, rs); } }
2.4 jdbc工具类的抽取
分析:每次进行数据库连接都会有连接和释放资源,每次都要编写代码过于麻烦,因此可以抽取一个JDBC工具类进行数据库连接和资源释放,可以设置为静态,通过类名调用:public class JDBCUtil { private static String url = "jdbc:mysql://localhost:3306/testJDBC"; private static String user = "root"; private static String password = "root"; static{ try { Class.forName("com.mysql.jdbc.Driver"); } catch (Exception 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) { // TODO Auto-generated catch block e.printStackTrace(); throw new RuntimeException(); } } //释放资源 public static void close(Connection conn,Statement stmt,ResultSet rs){ 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(); } } if(stmt!=null){ try { stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } }
3.PreparedStatement对象执行SQL操作
3.1增删查改
//查询操作 private static void testSelect() { Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { //获取连接 conn = JDBCUtil.getConn(); //定义预编译sql语句,动态sql String sql = "select * from student where id=?;"; //将sq发送到数据库端,检查sql语法及用户权限等信息 stmt = conn.prepareStatement(sql); //设置参数 stmt.setInt(1, 1); rs = stmt.executeQuery(); while(rs.next()){ System.out.println(rs.getInt("id")+"--"+rs.getString("name")); } } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } } //删除操作 private static void testDelete() { Connection conn = null; PreparedStatement stmt = null; try { //获取连接 conn = JDBCUtil.getConn(); //定义预编译sql语句,动态sql String sql = "delete from student where id=?;"; //将sq发送到数据库端,检查sql语法及用户权限等信息 stmt = conn.prepareStatement(sql); //设置参数 stmt.setInt(1, 1); int count = stmt.executeUpdate(); System.out.println(count); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } } //修改操作 private static void testUpdate() { Connection conn = null; PreparedStatement stmt = null; try { //获取连接 conn = JDBCUtil.getConn(); //定义预编译sql语句,动态sql String sql = "update student set name=? where id=?;"; //将sq发送到数据库端,检查sql语法及用户权限等信息 stmt = conn.prepareStatement(sql); //设置参数 stmt.setString(1, "s2"); stmt.setInt(2, 1); int count = stmt.executeUpdate(); System.out.println(count); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } } //插入操作 private static void testInsert() { Connection conn = null; PreparedStatement stmt = null; try { //获取连接 conn = JDBCUtil.getConn(); //定义预编译sql语句,动态sql String sql = "insert into student values(?,?,?);"; //将sq发送到数据库端,检查sql语法及用户权限等信息 stmt = conn.prepareStatement(sql); //设置参数 stmt.setInt(1, 1); stmt.setString(2, "s1"); stmt.setInt(3, 15); int count = stmt.executeUpdate(); System.out.println(count); } catch (Exception e) { e.printStackTrace(); throw new RuntimeException(); }finally{ JDBCUtil.close(conn, stmt, null); } }
4.CallableStatement对象执行存储过程
4.1 执行带输入参数的存储过程
分析:这里的pro_testIn是一个带输入参数的存储过程,存储过程见文章:java学习(20) -- 数据库MySql(二)
private static void testIn() { Connection conn = null; ResultSet rs = null; CallableStatement stmt = null; try { //获取连接对象 conn = JDBCUtil.getConn(); //定义sql语句 String sql = "CALL pro_testIn(?);"; stmt = conn.prepareCall(sql); //设置参数 stmt.setInt(1, 2); //发送到mysql服务器 rs = stmt.executeQuery(); //打印数据 while(rs.next()){ System.out.println(rs.getInt(1)+"-"+rs.getString(2)); } } catch (Exception e) { // TODO: handle exception }finally{ JDBCUtil.close(conn, stmt, rs); } }
4.2 执行带有输出参数的存储过程
注意:带有输出参数的存储过程,需要注册输出参数的类型
private static void testOut() { Connection conn = null; ResultSet rs = null; CallableStatement stmt = null; try { conn = JDBCUtil.getConn(); String sql = "CALL pro_testByOut(?,?);"; stmt = conn.prepareCall(sql); //给问号赋值 stmt.setInt(1, 2); //如果存储过程带有输出参数的时候,首先需要注册,输出参数的类型 stmt.registerOutParameter(2, java.sql.Types.VARCHAR); stmt.executeQuery(); System.out.println(stmt.getString(2)); } catch (Exception e) { // TODO: handle exception }finally{ JDBCUtil.close(conn, stmt, null); } }