概念:JDBC即Java DataBase Connectivity,Java数据库连接,Java语言操作数据库
本质:是官方(sun公司)定义的一套所有关系型数据库的规则,即接口。各个数据库厂商去实现这套接口,提供数据库驱动jar包。我们可以使用这套接口(JDBC)编程,真正执行的代码是驱动jar包中的实现类
步骤 :
- 导入驱动jar包mysql-connector-java-8.0.29.jar
- 复制mysql-connector-java-8.0.29.jar到项目新建的libs目录下
- 右键libs目录—>Add as library
- 注册驱动
- 获取数据库连接对象 Connection
- 定义sql语句
- 获取执行sql语句的对象 Statement
- 执行sql,接收返回结果
- 处理结果
- 释放资源
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
/**
* @ClassName JDBCDemo1
* @Description TODO JDBC快速入门
* @Author Mark
* @Date 2022/7/18 19:20
* @Version 1.0
*/
public class JDBCDemo1 {
public static void main(String[] args) throws Exception {
//1.导入驱动jar包
//2.注册驱动
//Class.forName("com.mysql.jdbc.Driver");
//com.mysql.jdbc.Driver已经被弃用,现在使用com.mysql.cj.jdbc.Driver,而且驱动程序会通过SPI自动注册,不再需要手动注册
//3.获取数据库连接对象
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bjpowernode", "root", "123");
//4.定义sql语句
String sql="update emp set sal = 1500 where empno=7369";
//5.获取执行sql的对象 Statement
Statement stmt = conn.createStatement();
//6.执行sql
int count = stmt.executeUpdate(sql);
//7.处理结果
System.out.println(count);//1
//释放资源
stmt.close();
conn.close();
}
}
3.1 DriverManager
驱动管理对象
- 功能:
- 注册驱动
static void registerDriver(Driver driver)
注册与给定的驱动程序 DriverManager 。 而在代码中使用Class.forName("com.mysql.jdbc.Driver");
注册 该class源码中使用DriverManager.registerDriver
注册驱动 在MySQL5之后可以不手动注册驱动 - 获取数据库连接
- 方法:
static Connection getConnection(String url, String user, String password)
尝试建立与给定数据库URL的连接。 - 参数:
- url:指定连接路径
- 语法:
jdbc:mysql://ip地址(或域名):端口号/数据库名称
- eg:
jdbc:mysql://localhost:3306/bjpowernode
- 注意:如果连接的是本机mysql服务器,并且mysql服务默认端口是3306,则url可以简写为
jdbc:mysql:///bjpowernode
- 语法:
- user:用户名
- password:密码
- 方法:
3.2 Connection
数据库连接对象
- 功能:
- 获取执行sql的对象:
Statement createStatement()
创建一个 Statement对象,用于将SQL语句发送到数据库。PrepareStatement prepareStatement(String sql)
创建一个 PreparedStatement对象,用于将参数化的SQL语句发送到数据库。
- 管理事务:
- 开启事务:
setAutoCommit(boolean autoCommit)
调用该方法设置参数为false,即开启事务 - 回滚事务:
rollback()
- 提交事务:
commit()
- 开启事务:
3.3 Statement
执行静态SQL语句并返回其生成的结果的对象
- 功能:执行sql
boolean execute(String sql)
执行给定的SQL语句,可能会返回多个结果。int executeUpdate(String sql)
执行DML语句(insert update delete)、DDL语句(create、alter、drop)- 返回值为int,是影响的行数,判断DML语句是否执行成功,返回值>0则成功
ResultSet executeQuery(String sql)
执行DQL语句(select),返回结果集对象- 练习:增删改不同的语句
- emp表 添加一条记录
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @ClassName JDBCDemo2
* @Description TODO department表 添加一条记录
* @Author Mark
* @Date 2022/7/18 23:58
* @Version 1.0
*/
public class JDBCDemo2 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
try {
// Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///studenttest", "root", "123");
stmt = conn.createStatement();
String sql = "insert into emp values('张三',1,19,2)";
int count = stmt.executeUpdate(sql);
if (count > 0) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//stmt.close();
//避免空指针异常
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- emp表 修改一条记录
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @ClassName JDBCDemo3
* @Description TODO emp表 修改一条记录
* @Author Mark
* @Date 2022/7/19 0:54
* @Version 1.0
*/
public class JDBCDemo3 {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
conn = DriverManager.getConnection("jdbc:mysql:///studenttest", "root", "123");
stmt = conn.createStatement();
int count = stmt.executeUpdate("update emp set age = 20 where id = 1");
if (count > 0) {
System.out.println("修改成功");
} else {
System.out.println("失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- emp表 删除一条记录
package com.JDBC;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @ClassName JDBCDemo4
* @Description TODO emp表 删除一条记录
* @Author Mark
* @Date 2022/7/19 1:04
* @Version 1.0
*/
public class JDBCDemo4 {
public static void main(String[] args) {
Statement stmt=null;
Connection conn=null;
try {
conn = DriverManager.getConnection("jdbc:mysql:///studenttest", "root", "123");
stmt = conn.createStatement();
String sql = "delete from emp where id = 1";
int count = stmt.executeUpdate(sql);
if (count > 0) {
System.out.println("删除成功");
} else {
System.out.println("失败");
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null){
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
3.4 ResultSet
结果集对象,封装查询结果
- ResultSet用到的方法:
boolean next()
:游标向下移动一行,判断当前行是否为最后一行末尾(是否有数据),如果是返回false,如果不是,则返回truegetxxx(参数)
:获取数据- xxx代表数据类型 如:
int getInt()
String getString()
- 参数有两种情况:
- int:代表列的编号,从1开始 如:getString(1)
- String:代表列的名称 如:getDouble(“sal”)
- xxx代表数据类型 如:
package com.JDBC;
import java.sql.*;
/**
* @ClassName JDBCDemo5
* @Description TODO
* @Author Mark
* @Date 2022/7/19 23:06
* @Version 1.0
*/
public class JDBCDemo5 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:mysql:///bjpowernode", "root", "123");
stmt = conn.createStatement();
String sql = "select * from emp";
rs = stmt.executeQuery(sql);
//让游标向下移动一行
rs.next();
//获取数据
int empno = rs.getInt(1);
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
Date hiredate = rs.getDate("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
System.out.println(empno + "---" + ename + "---" + job + "---" + mgr + "---" + hiredate + "---" + sal + "---" + comm + "---" + deptno);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
- ResultSet遍历结果集
- 使用时步骤:
- 游标向下移动一行
- 判断是否有数据
- 获取数据
package com.JDBC;
import java.sql.*;
/**
* @ClassName JDBCDemo6
* @Description TODO
* @Author Mark
* @Date 2022/7/20 10:44
* @Version 1.0
*/
public class JDBCDemo6 {
public static void main(String[] args) {
Statement stmt = null;
Connection conn = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection("jdbc:mysql:///bjpowernode", "root", "123");
stmt = conn.createStatement();
String sql = "select * from emp";
rs = stmt.executeQuery(sql);
//让游标向下移动一行
/*
if (rs.next()){
int empno = rs.getInt(1);
String ename = rs.getString("ename");
String job = rs.getString("job");
double sal = rs.getDouble("sal");
System.out.println(empno + "---" + ename + "---" + job + "---" + sal);
}
*/
while(rs.next()){
//循环判断是否有下一行(判断游标是否是最后一行末尾)
//获取数据
int empno = rs.getInt(1);
String ename = rs.getString("ename");
String job = rs.getString("job");
double sal = rs.getDouble("sal");
System.out.println(empno + "---" + ename + "---" + job + "---" + sal);
}
/*
for (; rs.next(); ) {
int empno = rs.getInt(1);
String ename = rs.getString("ename");
String job = rs.getString("job");
double sal = rs.getDouble("sal");
System.out.println(empno + "---" + ename + "---" + job + "---" + sal);
}
*/
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
</code></pre>
<ul>
<li>练习:</li>
<li>定义一个方法,查询emp表的数据,将其封装为对象,然后装载集合,返回。<ul>
<li>定义一个Emp类</li>
<li>查询定义方法 public List</li>
<li>实现方法 select * from emp;</li>
</ul>
</li>
</ul>
<pre><code class="language-java">package com.JDBCDoMain;
import java.util.Date;
/**
* @ClassName Emp
* @Description TODO 封装Emp表数据的JavaBean
* @Author Mark
* @Date 2022/7/20 11:09
* @Version 1.0
*/
public class Emp {
private int EmpNO;
private String EName;
private String Job;
private int Mgr;
private Date HireDate;
private double Sal;
private double Comm;
private int DeptNo;
public int getEmpNO() {
return EmpNO;
}
public void setEmpNO(int empNO) {
EmpNO = empNO;
}
public String getEName() {
return EName;
}
public void setEName(String EName) {
this.EName = EName;
}
public String getJob() {
return Job;
}
public void setJob(String job) {
Job = job;
}
public int getMgr() {
return Mgr;
}
public void setMgr(int mgr) {
Mgr = mgr;
}
public Date getHireDate() {
return HireDate;
}
public void setHireDate(Date hireDate) {
HireDate = hireDate;
}
public double getSal() {
return Sal;
}
public void setSal(double Sal) {
this.Sal = Sal;
}
public double getComm() {
return Comm;
}
public void setComm(double comm) {
Comm = comm;
}
public int getDeptNo() {
return DeptNo;
}
public void setDeptNo(int deptNo) {
DeptNo = deptNo;
}
@Override
public String toString() {
return "Emp{" +
"EmpNO=" + EmpNO +
", EName='" + EName + '\'' +
", Job='" + Job + '\'' +
", Mgr=" + Mgr +
", HireDate=" + HireDate +
", Sal=" + Sal +
", Comm=" + Comm +
", DeptNo=" + DeptNo +
'}';
}
}
</code></pre>
<pre><code class="language-java">package com.JDBC;
import com.JDBCDoMain.Emp;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName JDBCDemo7
* @Description TODO 练习 定义一个方法,查询emp表的数据,将其封装为对象,然后装载集合,返回。
* @Author Mark
* @Date 2022/7/20 11:18
* @Version 1.0
*/
public class JDBCDemo7 {
public static void main(String[] args) {
List list = new JDBCDemo7().findAll();
System.out.println(list);
System.out.println(list.size());
}
</code></pre>
<pre><code>public List<emp> findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List<emp> list = null;
try {
conn = DriverManager.getConnection("jdbc:mysql:///bjpowernode", "root", "123");
stmt = conn.createStatement();
String sql = "select * from emp";
rs = stmt.executeQuery(sql);
Emp emp = null;//不在while中创建,因为会导致额外使用栈内存
list = new ArrayList<emp>();
while (rs.next()) {
int EmpNo = rs.getInt(1);
String EName = rs.getString("ename");
String Job = rs.getString("job");
int Mgr = rs.getInt("mgr");
Date HireDate = rs.getDate("hiredate");
double Sal = rs.getDouble("sal");
double Comm = rs.getDouble("comm");
int DeptNo = rs.getInt("deptno");
//创建emp对象并赋值
emp = new Emp();
emp.setEmpNO(EmpNo);
emp.setEName(EName);
emp.setJob(Job);
emp.setMgr(Mgr);
emp.setHireDate(HireDate);
emp.setSal(Sal);
emp.setComm(Comm);
emp.setDeptNo(DeptNo);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
return list;
}
</emp></emp></emp>
</code></pre>
<p>}
3.5 PreparedStatement
预编译的SQL语句的对象(执行动态SQL语句)
- sql注入问题:在拼接sql时,有一些sql的特殊关键字参与字符串的拼接,会造成安全问题
- 用户名随便输入,输入密码:a' or 'a' ='a sql:
select * from user where username='dsafasfasgf' and password = 'a' or 'a' ='a'
- 解决sql注入问题:使用PreparedStatement对象来解决
- 预编译sql:参数使用?作为占位符
- 步骤:
- 导入驱动jar包 mysql-connector-java-8.0.29.jar
- 复制mysql-connector-java-8.0.29.jar到项目新建的libs目录下
- 右键libs目录--->Add as library
- 获取数据库连接对象 Connection
- ※定义sql语句
- 注意:sql的参数使用?作为占位符。如
select * from user where username = ? and password = ?
- ※获取执行sql语句的对象 prepareStatement
Connection.prepareStatement(String sql)
prepareStatement(String sql)
创建一个对象,用于将参数化的SQL语句发送到数据库
- 给?赋值
- 方法:setxxx(参数1,参数2)
- 参数1是?的位置编号,从1开始
- 参数2是?的值
- 执行sql,接收返回结果,不需要传递sql语句
- 处理结果
- 释放资源
package com.JDBC;
import com.util.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
/**
* @ClassName JDBCDemo9
* @Description TODO 练习 登陆案例
* @Author Mark
* @Date 2022/7/20 15:41
* @Version 1.0
*/
public class JDBCDemo10 {
public static void main(String[] args) {
//1.键盘录入,接收用户名密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
//2.调用方法
boolean flag = new JDBCDemo10().login(username, password);
//3.判断结果
if (flag) {
System.out.println("登陆成功");
} else {
System.out.println("用户名或密码错误");
}
}
//登陆方法
public boolean login(String username, String password) {
if (username == null) {
return false;
}
//连接数据库登陆数据库
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
String sql = "select * from user where username=? and password=?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
rs = pstmt.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, pstmt, conn);
}
return false;
}
}
注意: 后期都会使用PreparedStatement来完成增删改查的所有操作
- 可以有效防止SQL注入
- 效率更高
抽取JDBC工具类:JDBCUtils
- 目的:简化书写
- 分析:
- 注册驱动
- 抽取一个方法获取连接对象
- 需求:不想传递参数(繁琐),还得保证工具类的通用性
- 解决:配置文件
- jdbc.properties url= user= password=
- 抽取一个方法释放资源
package com.util;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;
/**
* @ClassName JDBCUtils
* @Description TODO JDBC工具类
* @Author Mark
* @Date 2022/7/20 11:49
* @Version 1.0
*/
public class JDBCUtils {
private static String url;
private static String username;
private static String password;
//配置文件的读取,读取一次,拿到连接数据库需要的值。使用静态代码块
static {
//读取资源文件,获取值
try {
//1.创建Properties集合类
Properties pro = new Properties();
//2.加载文件
// pro.load(new FileReader("src/jdbc.properties"));
//获取src路径下文件的方式--ClassLoader
ClassLoader classLoader = JDBCUtils.class.getClassLoader();
URL resource = classLoader.getResource("jdbc.properties");
String path = resource.getPath();
pro.load(new FileReader(path));
//3.获取数据,赋值
url = pro.getProperty("url");
username = pro.getProperty("username");
password = pro.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接
*
* @return 连接对象
*/
public static Connection getConnection(/*String url,String user,String password*/) throws SQLException {
// DriverManager.getConnection(url,user,password);
return DriverManager.getConnection(url, username, password);
}
/**
* 释放资源
*/
public static void close(Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 释放资源
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
package com.JDBC;
import com.JDBCDoMain.Emp;
import com.util.JDBCUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName JDBCDemo7
* @Description TODO 演示jdbc工具类
* @Author Mark
* @Date 2022/7/20 11:18
* @Version 1.0
*/
public class JDBCDemo8 {
public static void main(String[] args) {
List list = new JDBCDemo8().findAll();
System.out.println(list);
System.out.println(list.size());
}
public List findAll() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
List list = null;
try {
// conn = DriverManager.getConnection("jdbc:mysql:///bjpowernode", "root", "123");
conn = JDBCUtils.getConnection();
stmt = conn.createStatement();
String sql = "select * from emp";
rs = stmt.executeQuery(sql);
Emp emp = null;//不在while中创建,因为会导致额外使用栈内存
list = new ArrayList();
while (rs.next()) {
int EmpNo = rs.getInt(1);
String EName = rs.getString("ename");
String Job = rs.getString("job");
int Mgr = rs.getInt("mgr");
Date HireDate = rs.getDate("hiredate");
double Sal = rs.getDouble("sal");
double Comm = rs.getDouble("comm");
int DeptNo = rs.getInt("deptno");
//创建emp对象并赋值
emp = new Emp();
emp.setEmpNO(EmpNo);
emp.setEName(EName);
emp.setJob(Job);
emp.setMgr(Mgr);
emp.setHireDate(HireDate);
emp.setSal(Sal);
emp.setComm(Comm);
emp.setDeptNo(DeptNo);
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// if (rs != null) {
// try {
// rs.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
//
// if (stmt != null) {
// try {
// stmt.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
//
// if (conn != null) {
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
JDBCUtils.close(rs, stmt, conn);
}
return list;
}
}
- 练习
- 需求:
- 通过键盘录入用户名和密码
- 判断用户是否登陆成功
- 如果成功提示登陆成功,失败提示登陆失败
- 步骤
- 创建一个数据库user表
CREATE TABLE user(
id int PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(32),
password VARCHAR(32)
);
DESC user;
INSERT INTO user VALUES(null,'zhangsan','123');
INSERT INTO user VALUES(null,'lisi','234');
SELECT * FROM user;
package com.JDBC;
import com.util.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
/**
* @ClassName JDBCDemo9
* @Description TODO 练习 登陆案例
* @Author Mark
* @Date 2022/7/20 15:41
* @Version 1.0
*/
public class JDBCDemo9 {
public static void main(String[] args) {
//1.键盘录入,接收用户名密码
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名");
String username = sc.nextLine();
System.out.println("请输入密码");
String password = sc.nextLine();
//2.调用方法
boolean flag = new JDBCDemo9().login(username, password);
//3.判断结果
if (flag) {
System.out.println("登陆成功");
} else {
System.out.println("用户名或密码错误");
}
}
//登陆方法
public boolean login(String username, String password) {
if (username == null) {
return false;
}
//连接数据库登陆数据库
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
stmt = conn.createStatement();
String sql = "select * from user where username='" + username + "' and password='" + password + "'";
rs = stmt.executeQuery(sql);
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(rs, stmt, conn);
}
return false;
}
}
+ 以上程序存在风险,sql注入:
请输入用户名
dsafasfasgf
请输入密码
a' or 'a' = 'a
登陆成功
- 事务:一个包含多个步骤的业务操作。如果这个业务操作被事务管理,则这多个步骤要么同时成功,要么同时失败
- 操作:
- 开启事务
- 回滚事务
- 提交事务
- 使用Connection对象来管理事务
- 开启事务:
setAutoCommit(boolean autoCommit)
调用该方法设置参数为false,即开启事务
- 执行sql之前开启事务
- 回滚事务:
rollback()
- 在catch中回滚事务
- 提交事务:
commit()
- 所有sql都执行完提交事务
- 实现银行转账案例
*
package com.JDBC;
import com.util.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @ClassName JDBCDemo11
* @Description TODO 事务
* @Author Mark
* @Date 2022/7/20 16:50
* @Version 1.0
*/
public class JDBCDemo11 {
public static void main(String[] args) {
boolean flag = new JDBCDemo11().transfer("zhangsan", 500, "lisi");
if (flag) {
System.out.println("转账成功");
} else {
System.out.println("转账失败");
}
}
public boolean transfer(String name1, double account, String name2) {
if (name1 == null || name2 == null) {
return false;
}
Connection conn = null;
PreparedStatement pstmt1 = null;
PreparedStatement pstmt2 = null;
try {
conn = JDBCUtils.getConnection();
conn.setAutoCommit(false);
String sql1 = "update account set balance = balance-? where name= ?";
String sql2 = "update account set balance = balance+? where name= ?";
pstmt1 = conn.prepareStatement(sql1);
pstmt1.setDouble(1, account);
pstmt1.setString(2, name1);
pstmt2 = conn.prepareStatement(sql2);
pstmt2.setDouble(1, account);
pstmt2.setString(2, name2);
int flag1 = pstmt1.executeUpdate();
// int i= 3/0;//手动制造异常
int flag2 = pstmt2.executeUpdate();
conn.commit();
if (flag1 == 1 && flag2 == 1) {
return true;
}
} catch (/*SQLException e*/Exception e) {
//事务回滚
try {
if (conn != null) {
conn.rollback();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} finally {
JDBCUtils.close(pstmt1, conn);
JDBCUtils.close(pstmt2, null);
}
return false;
}
}
- 概念:其实就是一个容器(集合),存放数据库连接的容器
- 当系统初始化好后,容器被创建,容器中会申请一些连接对象,当用户来访问数据库时,从容器中获取连接对象,用户访问之后,会将连接对象还给容器
- 好处:
- 节约系统资源
- 用户访问高效
- 实现:
- 标准接口:DataSource javax.sql包下的
- 方法:
- 获取连接:
getConnection()
- 归还连接:
close()
如果连接对象Connection是从连接池中获取的,那么调用Connection.close()方法,则不会再关闭连接了,而是归还连接
- 一般我们不去实现它,由数据库厂商来实现
- C3P0:数据库连接池技术(较老)
- Druid:德鲁伊数据库连接池实现技术,由阿里巴巴提供(全球最好的数据库连接池技术之一)
6.1 C3P0的使用
- 步骤:
- 导入jar包 c3p0-0.9.5.2.jar mchange-commons-java-0.2.12.jar( 不要忘记数据库驱动jar包)
- 定义配置文件
c3p0.properties
或者 c3p0-config.xml
名字固定,会自动加载
- 路径:直接将文件放在src目录下即可
com.mysql.cj.jdbc.Driver
jdbc:mysql://localhost:3306/db3
root
123
5
10
3000
com.mysql.cj.jdbc.Driver
jdbc:mysql://localhost:3306/db3
root
123
5
8
1000
- 创建核心对象 数据库连接池对象 ComboPooledDataSource
- 获取连接:getConnection
package com.dataSource.c3p0;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
/**
* @ClassName C3P0Demo
* @Description TODO C3P0的演示
* @Author Mark
* @Date 2022/7/20 18:01
* @Version 1.0
*/
public class C3P0Demo {
public static void main(String[] args) throws SQLException {
//1.创建数据库连接池对象
DataSource ds=new ComboPooledDataSource();
//2.获取连接对象
Connection conn = ds.getConnection();
//3.打印
System.out.println(conn);
}
}
6.2 Druid的使用
- 导入jar包druid-1.2.9.jar
- 定义配置文件:
- 是properties形式的
- 可以叫任意名称,可以放在任意目录下,但是需要手动加载
- 加载配置文件
- 获取数据库连接池对象:通过工厂类来获取
DruidDataSourceFactory
- 获取连接:
getConnection
package com.dataSource.druid;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
/**
* @ClassName DruidDemo01
* @Description TODO Druid演示
* @Author Mark
* @Date 2022/7/20 19:42
* @Version 1.0
*/
public class DruidDemo01 {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3.加载配置文件
Properties pro=new Properties();
InputStream inputStream = DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties");
pro.load(inputStream);
//4.获取连接池对象
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
//5.获取连接
Connection conn = ds.getConnection();
System.out.println(conn);
}
}
- 定义一个类DruidUtils
- 提供静态代码块加载配置文件,初始化连接对象
- 提供方法
- 获取连接方法:通过数据库连接池获取连接
- 释放资源
- 获取连接池的方法
package com.util;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.dataSource.druid.DruidDemo01;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @ClassName DruidUtils
* @Description TODO DruidUtils工具类
* @Author Mark
* @Date 2022/7/20 19:54
* @Version 1.0
*/
public class DruidUtils {
//1.定义成员变量 DataSource
private static DataSource ds;
static {
try {
//1.加载配置文件
Properties pro = new Properties();
InputStream inputStream = DruidDemo01.class.getClassLoader().getResourceAsStream("druid.properties");
//2.获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
/**
* 释放资源
* @param stmt
* @param conn
*/
public static void close(Statement stmt, Connection conn) {
// if (stmt!= null){
// try {
// stmt.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
//
// if (conn!= null){
// try {
// conn.close();
// } catch (SQLException e) {
// e.printStackTrace();
// }
// }
close(null, stmt, conn);
}
/**
* 释放资源重载
* @param rs
* @param stmt
* @param conn
*/
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 获取连接池
* @return ds
*/
public static DataSource getDataSource() {
return ds;
}
}
package com.dataSource.druid;
import com.util.DruidUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @ClassName DruidDemo02
* @Description TODO Druid工具类测试
* @Author Mark
* @Date 2022/7/20 20:06
* @Version 1.0
*/
public class DruidDemo02 {
public static void main(String[] args) {
//完成添加操作,给account添加一条记录
Connection conn = null;
PreparedStatement pstmt = null;
try {
//1.获取连接
conn = DruidUtils.getConnection();
//2.定义sql
String sql = "insert into account values(null,?,?)";
//3.获取pstmt对象
pstmt = conn.prepareStatement(sql);
//4.给?赋值
pstmt.setString(1,"wangwu");
pstmt.setDouble(2,3000);
//5.执行sql
int count = pstmt.executeUpdate();
System.out.println(count);
} catch (SQLException e) {
e.printStackTrace();
}finally {
//6.释放资源
DruidUtils.close(pstmt,conn);
}
}
}
7.1 JdbcTemplate简单介绍
- Spring框架对JDBC的简单封装。提供了一个JdbcTemplate对象简化JDBC的开发
- 步骤:
- 导入jar包
- 创建JdncTemplate对象。依赖于数据源DataSource
JdbcTemplate template = new JdbcTemplate(ds);
- 调用JdbcTemplate的方法来完成CRUD的操作
- update():执行DML语句(增、删、改语句)
- queryForMap():查询结果,将结果封装为Map集合。将列名作为key,将值作为value,将这条记录封装为一个Map集合
- queryForList():查询结果,将结果封装为list集合。将每一条记录封装为Map集合,再将Map集合装载到一个List集合中
- query():查询结果,将结果封装为JavaBean对象
- qurey的参数:RowMapper
- 一般我们使用BeanPropertyRowMapper实现类,可以完成数据到JavaBean的自动封装
new BeanPropertyRowMapper(Emp.class)
+ queryForObject:查询结果,将结果封装为对象
* 一般用于聚合函数的查询
7.2 JdbcTemplate快速入门
package com.JDBCTemplate;
import com.util.DruidUtils;
import org.springframework.jdbc.core.JdbcTemplate;
/**
* @ClassName JDBC
* @Description TODO JDBCTemplate入门
* @Author Mark
* @Date 2022/7/20 20:37
* @Version 1.0
*/
public class JDBCTemplateDemo01 {
public static void main(String[] args) {
//1.导入jar包
//2.创建JDBCTemplate对象
JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
//3.调用方法
String sql = "update account set balance = 5000 where id = ?";
int count = template.update(sql, 3);
System.out.println(count);
}
}
7.3 练习:JdbcTemplate执行DML语句
- 需求:
- 修改SMITH的sal为1800
- 添加一条记录
- 三处刚才添加的记录
- 查询SMITH的记录,将其封装为Map集合
- 查询所有记录,将其封装为List集合
- 查询所有记录,将其封装为Emp对象的List集合
- 查询总记录数
package com.JDBCTemplate;
import com.TemplateDomain.Emp;
import com.util.DruidUtils;
import org.junit.Test;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
/**
* @ClassName JDBCTemplateDemo02
* @Description TODO
* @Author Mark
* @Date 2022/7/20 23:40
* @Version 1.0
*/
public class JDBCTemplateDemo02 {
private JdbcTemplate template = new JdbcTemplate(DruidUtils.getDataSource());
@Test
public void test1(){
// template = new JdbcTemplate(DruidUtils.getDataSource());
String sql="update emp set sal = 1800 where ename='SMITH'";
int count = template.update(sql);
System.out.println(count);
}
@Test
public void test2(){
String sql="insert into emp(empno,ename,sal,deptno) values(?,?,?,?)";
int count = template.update(sql,7799,"MARK",9921,20);
System.out.println(count);
}
@Test
public void test3(){
String sql="delete from emp where ename=?";
int count = template.update(sql, "mark");
System.out.println(count);
}
@Test
public void test4(){
String sql="select * from emp where ename = ?";
Map map = template.queryForMap(sql, "SMITH");
System.out.println(map);
}
//expected 1, actual 2
//queryForMap()查询的结果只能是1
@Test
public void test5(){
String sql="select * from emp where ename = ? or ename = ?";
Map map = template.queryForMap(sql, "SMITH","WARD");
System.out.println(map);
}
@Test
public void test6(){
String sql="select * from emp";
List> list = template.queryForList(sql);
//iter快捷键
for (Map stringObjectMap : list) {
System.out.println(stringObjectMap);
}
}
@Test
public void test7(){
String sql="select * from emp";
List list = template.query(sql, new RowMapper() {
@Override
public Emp mapRow(ResultSet rs, int i) throws SQLException {
Emp emp = new Emp();
int empno = rs.getInt("empno");
String ename = rs.getString("ename");
String job = rs.getString("job");
int mgr = rs.getInt("mgr");
Date hiredate = rs.getDate("hiredate");
double sal = rs.getDouble("sal");
double comm = rs.getDouble("comm");
int deptno = rs.getInt("deptno");
emp.setEmpNO(empno);
emp.setEName(ename);
emp.setJob(job);
emp.setMgr(mgr);
emp.setHireDate(hiredate);
emp.setSal(sal);
emp.setComm(comm);
emp.setDeptNo(deptno);
return emp;
}
});
for (Emp emp : list ) {
System.out.println(emp);
}
}
@Test
public void test7_2(){
String sql="select * from emp";
List list = template.query(sql, new BeanPropertyRowMapper(Emp.class));
for (Emp emp : list) {
System.out.println(emp);
}
}
@Test
public void test8(){
String sql="select count(empno) from emp";
Long total = template.queryForObject(sql,long.class);
System.out.println(total);
}
}
Original: https://www.cnblogs.com/hackertyper/p/16500458.html
Author: 风吹头蛋凉OvO
Title: JDBC
原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/575120/
转载文章受原作者版权保护。转载请注明原作者出处!