JDBC
-
JAVA Database Connectivity 定义了客户端如何访问数据库
-
JDBC操作数据库的步骤:
- 1.导入驱动jar包
- 2.驱动注册
- 3.获取数据库连接对象 Connection
- 4.定义SQL
- 5.获取执行SQL的对象 Statement
- 6.通过Statement对象执行sql语句 得到返回值
- 7.处理结果
- 8.释放资源 防止内存泄漏
-
驱动jar包
「mysql-connector-java-5.1.37-bin.jar」https://www.aliyundrive.com/s/aNahkdFt1F7 点击链接保存,或者复制本段内容,打开「阿里云盘」APP ,无需下载极速在线查看,视频原画倍速播放。
-
更改一条数据
import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; public class JdbcDemo01 { public static void main(String[] args) throws Exception { //导包 注册驱动 mysql5只会自动注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取数据库连接对象 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root"); //定义sql String sql="update student set age = 4999 where name ='zhongli'"; //获取执行sql的对象 Statement stmt = conn.createStatement(); //执行sql int count=stmt.executeUpdate(sql); //打印影响行数 System.out.println(count); //释放资源 stmt.close(); conn.close(); } }
-
详解:
-
驱动注册: 本文使用Mysql数据库 当然jdbc也可以操作其他数据库.这一步告诉程序使用哪个数据库驱动
Class.forName("com.mysql.jdbc.Driver");//注意 Mysql5之后可以省略这一步
-
获取数据库连接
static Connection getConnection(String url, String user, String password)
- url : jdbc:mysql://ip地址(域名):端口号/数据库名称
- 当连接本地数据库且端口号为默认的3306则可以简写为: jdbc:mysql:///数据库名称
- user: 用户名
- password: 密码
- url : jdbc:mysql://ip地址(域名):端口号/数据库名称
-
Connection:数据库连接对象
- 1.获取执行sql的对象 createStatement
- 2.管理事务
- setAutoCommit(boolean autoCommit) 调用该方法设置参数为false
- commit()
- rollback()
-
Statement: 执行sql对象
-
-
-
增加一条记录
import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; public class JdbcDemo02 { public static void main(String[] args) { Connection conn=null; Statement stat=null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库连接对象 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root"); //3.定义sql String sql= "insert into student values(null,'linghua',18,100,100)"; //4.获取执行sql对象 stat = conn.createStatement(); //5.执行sql int count = stat.executeUpdate(sql); if(count>0){ System.out.println("成功添加"); }else{ System.out.println("添加失败"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ if(stat!=null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
查询记录
//将查询到的记录保存到ResultSet中 import java.sql.*; public class JdbcDemo03 { public static void main(String[] args) { Connection conn = null; Statement stat = null; ResultSet rs = null; try { //1.注册驱动 Class.forName("com.mysql.jdbc.Driver"); //2.获取数据库连接对象 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb1", "root", "root"); //3.定义sql String sql = "select * from student"; //4.获取执行sql对象 stat = conn.createStatement(); //5.执行sql rs = stat.executeQuery(sql); while (rs.next()) { String name = rs.getString("name"); int age = rs.getInt(3); int math = rs.getInt(4); int cpp = rs.getInt(5); System.out.println(name + " " + age + " " + math + " " + cpp); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (stat != null) { try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } } }
-
编写工具类 并通过配置文件来连接数据库
//jdbc.properties url=jdbc:mysql://localhost:3306/mydb1 user=root password=root driver=com.mysql.jdbc.Driver
//为了提高代码重用 解耦 编写工具类 将数据库连接 资源释放提取出来 import java.io.FileReader; import java.io.IOException; import java.net.URL; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String url; private static String user; private static String password; private static String driver; static{ try { //1.创建Properties集合类 Properties pro=new Properties(); //获取src路径下的文件方式 ClassLoader ClassLoader classLoader = JdbcUtils.class.getClassLoader(); URL res= classLoader.getResource("jdbc.properties"); String path =res.getPath(); //加载配置文件 pro.load(new FileReader(path)); url=pro.getProperty("url"); user= pro.getProperty("user"); password= pro.getProperty("password"); driver=pro.getProperty("driver"); Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 获取连接 静态方法直接用类型调用 * @return 连接对象 * @throws SQLException */ public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url,user,password); } /** * 释放资源 * @param stat * @param conn */ public static void close(Statement stat, Connection conn){ if(stat!=null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } /** * 释放资源 * @param rs * @param stat * @param conn */ public static void close(ResultSet rs,Statement stat, Connection conn){ if(rs!=null){ try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if(stat!=null){ try { stat.close(); } catch (SQLException e) { e.printStackTrace(); } } if(conn!=null){ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
-
用工具类来查询所有记录
import JdbcUtils.JdbcUtils; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JdbcDome04 { public static void main(String[] args) { Connection conn=null; Statement stat=null; ResultSet rs=null; try { conn= JdbcUtils.getConnection(); stat=conn.createStatement(); String sql="select* from student"; rs = stat.executeQuery(sql); while(rs.next()){ String name = rs.getString("name"); int age = rs.getInt(3); int math = rs.getInt(4); int cpp = rs.getInt(5); double money = rs.getDouble(6); System.out.println(name+" "+age+" "+math+" "+cpp+" "+money); } } catch (SQLException e) { e.printStackTrace(); }finally{ JdbcUtils.close(rs,stat,conn); } } }
-
因为Statement对象不安全 --sql注入问题 引出prepareStatement对象
- sql注入: 在拼接sql时 会有一些sql特殊关键字参与拼接 造成安全性问题
- prepareStatement对象: 使用预编译sql: 参数使用?作为占位符 执行sql前要给?赋值
import JdbcUtils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class JdbcDemo5 { public static void main(String[] args) { Connection conn=null; PreparedStatement pstat1=null; PreparedStatement pstat2=null; try { conn = JdbcUtils.getConnection(); conn.setAutoCommit(false); String sql1="update student set money=money-? where id =?"; String sql2="update student set money=money+? where id=?"; pstat1 = conn.prepareStatement(sql1); pstat2 = conn.prepareStatement(sql2); pstat1.setDouble(1,500); pstat1.setInt(2,1); pstat2.setDouble(1,500); pstat2.setInt(2,2); pstat1.executeUpdate(); //int i=3/0; pstat2.executeUpdate(); conn.commit(); } catch (Exception e) { try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } e.printStackTrace(); }finally{ JdbcUtils.close(pstat1,conn); JdbcUtils.close(pstat2,null); } } }