JDBC简介(Java Database Connectivity)
概念:用Java语言操作关系型数据库的API
JDBC快速入门
步骤
0.创建工程,导入驱动jar包
mysql-connector-java-5.1.48.jar
1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
2.获取连接
Connection conn=DriverManager.getConnection(url,username,password);
3.定义SQL语句
4.获取执行SQL对象
Statement stmt=conn.createStatement();
5.执行SQL
stmt.executeUpdate(sql);
6.处理返回结果
7.释放资源
JDBC API详解
DriverManager(驱动管理类)
作用:注册驱动,获取数据库连接
建立连接:
/*参数 url:连接路径(jdbc:mysql://ip地址:端口号/数据库名称?参数键值对1&参数键值对2...) {连接本机的数据库的话,ip地址:端口号可省略} eg:jdbc:mysql:///数据库名称 user:用户名 password:密码 */ DriverManager.getConnection(url, username, password)
注册驱动:
//MySQL5.0后的版本已经可以自动注册驱动了,不需要写了 Class.forName("com.mysql.cj.jdbc.Driver"); 底层用DriverManager.registerDriver()实现的。
Connection(数据库连接对象)
作用:获取执行SQL对象,管理事务
获取执行SQL对象
1.普通执行SQL对象
Statement createStatement()
2.预编译SQL执行SQL对象:防止SQL注入
PrepareStatement prepareStatement(sql)
管理事务
MYSQL:
开启事务;BEGIN;/START TRANSANCTION; 提交事务:COMMIT; 回滚事务:ROLLBACK;
JDBC:Connection接口定义了3个对应方法。
开启事务:setAutoCommit(boolean autoCommit):true为自动提交事务;false为手动提交事务,即开启事务。 提交事务:commit() 回滚事务:rollback()
案例:
package com.hjy.jdbc; import com.mysql.jdbc.Driver; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; //快速入门 public class JDBCDemo { public static void main(String[] args) throws Exception { //1.注册驱动 // Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/demo"; String username = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, username, password); //3.定义SQL语句 String sql1 = "update student set number = 2000 where id=1"; String sql2 = "update student set number = 3000 where id=2"; //4.获取执行sql对象Statement Statement stmt = conn.createStatement(); try { //开启事务 conn.setAutoCommit(false); //5.执行sql int count1 = stmt.executeUpdate(sql1);//受影响的行数 //6.处理结果 System.out.println(count1); //第二条 int count2 = stmt.executeUpdate(sql2);//受影响的行数 //6.处理结果 System.out.println(count2); //提交事务 conn.commit(); } catch (Exception e) { //回滚事务 conn.rollback(); throw new RuntimeException(e); } //7.释放资源 stmt.close(); conn.close(); } }
可以从这个案例理解事务起到的的作用
1.在sql1执行完后,加入一条错误语句
package com.hjy.jdbc; import com.mysql.jdbc.Driver; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; //快速入门 public class JDBCDemo { public static void main(String[] args) throws Exception { //1.注册驱动 // Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/demo"; String username = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, username, password); //3.定义SQL语句 String sql1 = "update student set number = 2000 where id=1"; String sql2 = "update student set number = 3000 where id=2"; //4.获取执行sql对象Statement Statement stmt = conn.createStatement(); try { //开启事务 conn.setAutoCommit(false); //5.执行sql int count1 = stmt.executeUpdate(sql1);//受影响的行数 //6.处理结果 System.out.println(count1); int i=3/0; //第二条 int count2 = stmt.executeUpdate(sql2);//受影响的行数 //6.处理结果 System.out.println(count2); //提交事务 conn.commit(); } catch (Exception e) { //回滚事务 conn.rollback(); throw new RuntimeException(e); } //7.释放资源 stmt.close(); conn.close(); } }
2.使用事务,得到的结果是:sql1正确执行,但没有改变数据库的数据
3.不适用事务得到的结果是:sql1正确执行,且数据库数据已改变
Statement
作用:
1.执行SQL语句
int executeUpdate(sql):执行DML,DDL语句 返回值:1.DML影响的行数;2.DDL执行成功返回0 ResultSet executeQuery(sql):执行SQL语句 返回值:ResultSet结果集对象
DML语句:
String sql2 = "update student set number = 3000 where id=2";
DDL语句:
//3.定义SQL语句 String sql = "create database demo2";
ResultSet(结果集对象)
作用:封装了DQL查询语句的结果
ResultSet stmt.executeQuery(sql):执行SQL语句,返回ResultSet对象
获取查询结果
先向下移动一行再判断
boolean next():将光标从当前位置向前移动一行;判断当前行是否为有效行 返回值: true:有效行,当前行有数据
xxx getxxx(参数):获取数据 xxx:数据类型 参数: int:列的编号,从1开始 String:列的名称
案例1:
package com.hjy.jdbc; import org.junit.Test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; /* 执行DQL语句 */ public class JDBCDemo2_resaultset { @Test public void testResultSet() throws Exception { //1.注册驱动 // Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/demo"; String username = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, username, password); //sql String sql = "select * from student"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while(rs.next()){ int id = rs.getInt(1); String name = rs.getString(3); String gender = rs.getString(4); System.out.println(id); System.out.println(name); System.out.println(gender); System.out.println("----------------"); } rs.close(); stmt.close(); conn.close(); } }
案例2:查询student学生表数据,封装为Student对象中,并存储到ArrayList集合中。
1.建Student类
package com.hjy.pojo; public class Student { private int id; private String name; private String gender; public int getId() { return id; } public void setId(int id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } @Override public String toString() { return "Student{" + "id=" + id + ", name='" + name + '\'' + ", gender='" + gender + '\'' + '}'; } }
2.在JDBC_RESUALTSET中调用Student类
package com.hjy.jdbc; import com.hjy.pojo.Student; import org.junit.Test; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; import java.util.ArrayList; import java.util.List; /* 执行DQL语句 */ public class JDBCDemo2_resaultset { @Test public void testResultSet() throws Exception { //1.注册驱动 // Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/demo"; String username = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, username, password); //sql String sql = "select * from student"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt(1); String name = rs.getString(3); String gender = rs.getString(4); System.out.println(id); System.out.println(name); System.out.println(gender); System.out.println("----------------"); } rs.close(); stmt.close(); conn.close(); } /** * 查询student学生表数据,封装为Student对象中,并存储到ArrayList集合 * 1.定义实体类Student * 2.查询数据,封装到Student对象中 * 3.将Student对象存入Arraylist对象中 **/ @Test public void testResultSet2() throws Exception { //1.注册驱动 // Class.forName("com.mysql.cj.jdbc.Driver"); //2.获取连接 String url = "jdbc:mysql://127.0.0.1:3306/demo"; String username = "root"; String password = "123456"; Connection conn = DriverManager.getConnection(url, username, password); //sql String sql = "select * from student"; Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); List<Student> list = new ArrayList<>(); while (rs.next()) { Student student = new Student(); int id = rs.getInt(1); String name = rs.getString(3); String gender = rs.getString(4); student.setId(id); student.setName(name); student.setGender(gender); list.add(student); } System.out.println(list); rs.close(); stmt.close(); conn.close(); } }
PreparedStatement(解决sql注入)
作用:预编译sql语句并执行:预防sql注入问题
sql注入:通过操作输入来修改事先定义好的sql语句,用以达到执行代码对服务器进行攻击的方法。
通俗讲;
数据库连接池
定义:是一个容器,负责分配,管理数据库连接
它允许应用程序重复使用一个现有的数据库连接,而不是再重建一个。
使用:
标准接口:DataSource(获取连接)
Connection getConnection()
一种数据库连接池:Druid(德鲁伊)
使用步骤: 1.导入jar包druid-1.1.12.jar
2.定义配置文件
3.加载配置文件
4.获取数据库连接池对象
5.获取连接
完整代码案例:!需要注意文件输入流的路径是否正确!
package com.hjy.druid; //Druid连接池 import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.util.Map; import java.util.Properties; public class Druid_Demo { public static void main(String[] args) throws Exception { //1.导入jar包 //2.定义配置文件 //3.加载配置文件 Properties prop=new Properties(); prop.load(new FileInputStream("jdbc-demo/src/druid.properties")); //4.获取连接池对象 DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //5.获取数据库连接Connection Connection connection = dataSource.getConnection(); System.out.println(connection); //打印当前工作路径 // System.out.println(System.getProperty("user.dir")); } }
JDBC练习
完成品牌数据的增删改查操作
1.查询所有数据 2.添加品牌 3.根据id修改 4.根据id删除
1.准备环境: 数据库表 tb_brand
实体类 Brand
测试用例
完整的步骤: 1.获取其Connection 2.定义SQL 3.获取预编译PreparedStatement对象 4.设置参数 5.执行SQL 6.处理结果:list<Brand> 7.释放资源
1.查询
package com.hjy.example; import com.alibaba.druid.pool.DruidDataSourceFactory; import com.hjy.pojo.Brand; import org.junit.Test; import javax.sql.DataSource; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import java.util.Properties; //品牌数据增删改查测试 public class BrandTest { /** * 查询所有数据 * 1.SQL:select * from tb_brand; * 2.参数:不需要 * 3.结果:List<Brand> */ @Test public void testSelectAll() throws Exception { //1.获取Connection对象 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection conn = dataSource.getConnection(); //2.SQL语句 String sql = "select * from tb_brand;"; //3.获取预编译PreparedStatement对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4. //5.执行SQL ResultSet rs = pstmt.executeQuery(); List<Brand> brands=new ArrayList<>(); //6.处理结果 List<Brand> 封装Brand对象,装载List集合 while (rs.next()) { //获取数据 int id = rs.getInt("id"); String brandName = rs.getString("brand_name"); String companyName = rs.getString("company_name"); String description = rs.getString("description"); int status = rs.getInt("status"); //封装Brand对象 Brand brand = new Brand(); brand.setId(id); brand.setBrand_name(brandName); brand.setCompany_name(companyName); brand.setDescription(description); brand.setStatus(status); //装载集合 brands.add(brand); } System.out.println(brands); //7.释放资源 rs.close(); pstmt.close(); conn.close(); } }
2.添加
@Test /** * 查询所有数据 * 1.SQL:select * from tb_brand; * 2.参数:除id外的所有数据 * 3.结果:List<Brand> */ public void testAdd() throws Exception { //接收提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1; String description = "销量绕地球一圈"; int status = 1; //1.获取Connection对象 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection conn = dataSource.getConnection(); //2.SQL语句 String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values(?,?,?,?,?)"; //3.获取预编译PreparedStatement对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数 pstmt.setString(1, brandName); pstmt.setString(2, companyName); pstmt.setInt(3, ordered); pstmt.setString(4, description); pstmt.setInt(5, status); //5.执行SQL int count = pstmt.executeUpdate(); //6.处理结果 System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); }
3.删除
@Test /** * 查询所有数据 * 1.SQL: update tb_brand set brand_name=? company_name=? ordered=? description=? status=? where id=? * 2.参数:根据id删除数据 * 3.结果:boolean */ public void testUpdate() throws Exception { //接收提交的参数 String brandName = "香飘飘"; String companyName = "香飘飘"; int ordered = 1000; String description = "销量绕地球三圈"; int status = 1; int id = 4; //1.获取Connection对象 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection conn = dataSource.getConnection(); //2.SQL语句 String sql = "update tb_brand\n" + " set brand_name=?,\n" + " company_name=?,\n" + " ordered=?,\n" + " description=?,\n" + " status=?\n" + " where id=?"; //3.获取预编译PreparedStatement对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数 pstmt.setString(1, brandName); pstmt.setString(2, companyName); pstmt.setInt(3, ordered); pstmt.setString(4, description); pstmt.setInt(5, status); pstmt.setInt(6, id); //5.执行SQL int count = pstmt.executeUpdate(); //6.处理结果 System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); }
4.删除
@Test /** * 查询所有数据 * 1.SQL: delete from tb_brand where id=?; * 2.参数:id * 3.结果:boolean */ public void testDelete() throws Exception { //接收提交的参数 int id = 4; //1.获取Connection对象 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); Connection conn = dataSource.getConnection(); //2.SQL语句 String sql = "delete from tb_brand where id=?;\n"; //3.获取预编译PreparedStatement对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数 pstmt.setInt(1, id); //5.执行SQL int count = pstmt.executeUpdate(); //6.处理结果 System.out.println(count > 0); //7.释放资源 pstmt.close(); conn.close(); }
课程大总结
JDBC是一种使用Java语言操作数据库的接口。使用JDBC的一般步骤是:首先注册驱动(导入jar包;其次,使用数据库连接池,获取Connection连接,定义SQL语句;第三,获取预编译PreraredStatement对象;第四,执行SQL语句;第五,处理输出的结果;第六,释放资源。对上述的代码(4.删除)展开解释:
//1.创建一个Properties对象,用于加载数据库连接和Druid的配置信息。 Properties prop = new Properties(); prop.load(new FileInputStream("src/druid.properties")); DataSource dataSource = DruidDataSourceFactory.createDataSource(prop); //2.建立连接 Connection conn = dataSource.getConnection(); //定义sql语句,用String变量接收 String sql = "delete from tb_brand where id=?;\n"; //3.获取预编译PreparedStatement对象 PreparedStatement pstmt = conn.prepareStatement(sql); //4.设置参数:用到预编译返回的对象值,调用它的get,set方法 pstmt.setInt(1, id); //执行SQL int count = pstmt.executeUpdate(); //5.处理结果 System.out.println(count > 0); //6.释放资源 pstmt.close(); conn.close();
注:Druid配置文件内容示例:
包括url,用户名和密码,初始的连接个数,最大连接数
driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://127.0.0.1:3306/things username=root password=123456 initialSize=5 maxActive=10 maxWait=3000
[课程来源:黑马程序员JDBC]