JDBC开发流程
- 数据库连接字符串
数据库 | JDBC驱动类 | 连接字符串 |
---|---|---|
MySQL 5 | com.mysql.jdbc.Driver | jdbc:mysql://主机ip:端口/数据库名 |
MySQL 8 | com.mysql.cj.jdbc.Driver | jdbc:mysql://主机ip:端口/数据库名 |
Oracle | oracle.jdbc.driver.OracleDriver | jdbc:oracle:thin:@主机ip:端口:数据库名 |
SQL Server | com.mircosoft.sqlserver.jdbc.SQLServerDriver | jdbc:mircosoft:sqlserver:主机ip:端口;databasename=数据库名 |
- MySQL连接字符串常用参数
参数名 | 参数建议 | 说明 |
---|---|---|
useSSL | true、false | 是否禁用ssl |
useUnicode | true | 启用unicode编码传输数据 |
characterEncoding | UTF-8 | 使用UTF-8编码传输数据 |
serverTimezone | Asia/Shanghai | 使用东8时区时间,UTC+8 |
allowPublicKeyRetrieval | true | 允许从客户端获取公钥加密传输 |
package com.pro.jdbc.sample;
import java.sql.*;
import java.util.Scanner;
/**
* 标准JDBC操作五步骤
*/
public class StandJDBCSample{
public static void main(String[] args) {
System.out.println("请输入部门名称:");
Scanner in = new Scanner(System.in);
String pdname = in.next();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1、加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
/**
* CLass.forName用于加载指定的JDBC驱动类,本质是通知JDBC注册这个驱动类
* 驱动是由数据库的厂商自行开发的,连接字符串肯定是不同的
*/
//2、创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "123456");
//3、创建Statement对象
stmt = conn.createStatement();
//结果集
rs = stmt.executeQuery("select * from employee where dname='"+pdname+"'");
//4、遍历查询结果
//rs.next()返回布尔值,代表是否存在下一条记录
//如果有,返回true,同时结果集提取下一条记录
//如果没有,返回false,循环就会停止
while (rs.next()){
Integer eno = rs.getInt(1);//JDBC中的字段索引从1开始,而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname+"-"+eno+"-"+ename+"-"+salary);
}
//5、关闭连接,释放资源
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//5、关闭连接,释放资源
try {
if (rs != null){
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (stmt != null){
stmt.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null && conn.isClosed()){
conn.close();//其实conn物理底层连接断开,上面所有的资源自动释放
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
SQL注入攻击的应对
即 使用Statement类的子类PrepareStarement预处理,对sql语句中的符号进行转义处理
package com.pro.jdbc.hrapp.command;
import java.sql.*;
import java.util.Scanner;
public class PstmtQueryCommand implements Command{
@Override
public void execute() {
System.out.println("请输入部门名称:");
Scanner in = new Scanner(System.in);
String pdname = in.nextLine();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//1、加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "123456");
//3、创建Statement对象
String sql = "select * from employee where dname = ? ";//参数值用?替代,且不允许二次计算
// String sql = "select * from employee while dname=? and eno > ?";
pstmt = conn.prepareStatement(sql);
pstmt.setString(1,pdname);//注意参数索引从1开始
// pstmt.setInt(2,3500);
//结果集
rs = pstmt.executeQuery();
//4、遍历查询结果
//rs.next()返回布尔值,代表是否存在下一条记录
//如果有,返回true,同时结果集提取下一条记录
//如果没有,返回false,循环就会停止
while (rs.next()){
Integer eno = rs.getInt(1);//JDBC中的字段索引从1开始,而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname+"-"+eno+"-"+ename+"-"+salary);
}
//5、关闭连接,释放资源
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//5、关闭连接,释放资源
try {
if (rs != null){
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (pstmt != null){
pstmt.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null && conn.isClosed()){
conn.close();//其实conn物理底层连接断开,上面所有的资源自动释放
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
JDBC实现数据库数据的操作
员工管理系统
HumanResourceApplication.java
package com.pro.jdbc.hrapp;
import com.pro.jdbc.hrapp.command.*;
import java.util.Scanner;
public class HumanResourceApplication {
public static void main(String[] args) {
System.out.println("1-查询部门员工");
System.out.println("2-办理员工入职");
System.out.println("3-调整薪资");
System.out.println("4-员工离职");
System.out.println("5-分页查询员工数据");
System.out.println("请选择功能");
Scanner in = new Scanner(System.in);
Integer cmd = in.nextInt();
Command command =null;
switch (cmd){
case 1://查询部门员工
command = new PstmtQueryCommand();
command.execute();
break;
case 2:
command = new InsertCommand();
command.execute();
break;
case 3:
command = new UpdateCommand();
command.execute();
break;
case 4:
command = new DeleteCommand();
command.execute();
break;
case 5:
command = new PaginationCommand();
command.execute();
break;
}
}
}
封装Dbutils 工具类
package com.pro.jdbc.common;
import java.sql.*;
public class DbUtils {
/**
* 创建新的数据库连接
* @return 新的Connection对象
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws ClassNotFoundException, SQLException {
//1、加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
/**
* CLass.forName用于加载指定的JDBC驱动类,本质是通知JDBC注册这个驱动类
* 驱动是由数据库的厂商自行开发的,连接字符串肯定是不同的
*/
String url = "jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
//2、创建数据库连接
Connection conn= DriverManager.getConnection(url,"root","123456");
return conn;
}
/**
* 关闭连接,释放资源
* @param rs 结果集对象
* @param stmt Statement对象
* @param conn Connection对象
*/
public static void closeConnection(ResultSet rs, Statement stmt,Connection conn){
try {
if (rs != null){
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (stmt != null){
stmt.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null && conn.isClosed()){
conn.close();//其实conn物理底层连接断开,上面所有的资源自动释放
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
封装employee实体类
package com.pro.jdbc.hrapp.entity;
import java.util.Date;
/**
* 员工实体类
*/
public class Employee {
/**
* 1.具体默认构造函数
* 2.属性私有
* 3.存在getter和setter方法
*/
private Integer eno;
private String ename;
private Float salary;
private String dname;
private Date hiredate;
public Date getHiredate() {
return hiredate;
}
public void setHiredate(Date hiredate) {
this.hiredate = hiredate;
}
public Employee(){
}
public Integer getEno() {
return eno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public void setEno(Integer eno) {
this.eno = eno;
}
public String getDname() {
return dname;
}
public void setDname(String dname) {
this.dname = dname;
}
public Float getSalary() {
return salary;
}
public void setSalary(Float salary) {
this.salary = salary;
}
}
封装执行接口
package com.pro.jdbc.hrapp.command;
public interface Command {
public void execute();
}
查询部门员工实现类
package com.pro.jdbc.hrapp.command;
import java.sql.*;
import java.util.Scanner;
/**
* 查询员工数据
*/
public class QueryCommand implements Command{
@Override
public void execute() {
System.out.println("请输入部门名称:");
Scanner in = new Scanner(System.in);
String pdname = in.next();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//1、加载并注册JDBC驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2、创建数据库连接
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true", "root", "123456");
//3、创建Statement对象
stmt = conn.createStatement();
//结果集
rs = stmt.executeQuery("select * from employee where dname='"+pdname+"'");
//4、遍历查询结果
//rs.next()返回布尔值,代表是否存在下一条记录
//如果有,返回true,同时结果集提取下一条记录
//如果没有,返回false,循环就会停止
while (rs.next()){
Integer eno = rs.getInt(1);//JDBC中的字段索引从1开始,而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname+"-"+eno+"-"+ename+"-"+salary);
}
//5、关闭连接,释放资源
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
//5、关闭连接,释放资源
try {
if (rs != null){
rs.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (stmt != null){
stmt.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
if (conn != null && conn.isClosed()){
conn.close();//其实conn物理底层连接断开,上面所有的资源自动释放
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
办理员工入职实现类
package com.pro.jdbc.hrapp.command;
import com.pro.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Scanner;
/**
* 新增员工数据
*/
public class InsertCommand implements Command {
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工的编号:");
int eno = in.nextInt();
System.out.println("请输入员工的姓名:");
String ename = in.next();
System.out.println("请输入员工的薪资:");
float salary = in.nextFloat();
System.out.println("请输入隶属的部门:");
String dname = in.next();
System.out.println("请输入入职的日期:");
String strHiredate = in.next();
/**
* String 到java.sql.Date 分两步
*/
//1.String转为java.util.Date
java.util.Date udHiredate = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
try {
udHiredate = sdf.parse(strHiredate);
} catch (ParseException e) {
e.printStackTrace();
}
//2.java.util.Date 转为java.sql.Date
long time = udHiredate.getTime();//获取自1970年到现在的毫米数
java.sql.Date sdHiredate = new java.sql.Date(time);
Connection conn = null;
PreparedStatement pstmt= null;
try {
conn = DbUtils.getConnection();
String sql = "insert into employee(eno,ename,salary,dname,hiredate)values(?,?,?,?,?)";
pstmt= conn.prepareStatement(sql);
pstmt.setInt(1,eno);
pstmt.setString(2, ename);
pstmt.setFloat(3, salary);
pstmt.setString(4, dname);
pstmt.setDate(5,sdHiredate);//java.sql.Date
int cnt = pstmt.executeUpdate();//所有写操作都使用executeUpdate
System.out.println("cnt:" + cnt);
System.out.println(ename+"员工入职手续已办理");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
}
调整薪资实现类
package com.pro.jdbc.hrapp.command;
import com.pro.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 调整薪资
*/
public class UpdateCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工的编号:");
int eno = in.nextInt();
System.out.println("请输入员工新的薪资:");
float salary = in.nextFloat();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
String sql = "update employee set salary = ? where eno=?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,salary);
pstmt.setInt(2, eno);
int cnt = pstmt.executeUpdate();
if (cnt == 1){
System.out.println("员工薪资调整完毕");
}else{
System.out.println("未找到"+eno+"编号员工数据");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
}
员工离职实现类
package com.pro.jdbc.hrapp.command;
import com.pro.jdbc.common.DbUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;
/**
* 删除员工数据
*/
public class DeleteCommand implements Command{
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入员工的编号:");
int eno = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
try {
conn = DbUtils.getConnection();
String sql = "delete from employee where eno=?";
pstmt = conn.prepareStatement(sql);
pstmt.setFloat(1,eno);
int cnt = pstmt.executeUpdate();
if (cnt == 1){
System.out.println("员工离职手续已完成");
}else{
System.out.println("未找到"+eno+"编号员工数据");
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
}
分页查询员工数据实现类
package com.pro.jdbc.hrapp.command;
import com.pro.jdbc.common.DbUtils;
import com.pro.jdbc.hrapp.entity.Employee;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.sql.Date;
import java.util.List;
import java.util.Scanner;
/**
* 分页显示员工数据
*/
public class PaginationCommand implements Command {
@Override
public void execute() {
Scanner in = new Scanner(System.in);
System.out.println("请输入页号:");
int page = in.nextInt();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
List<Employee> list = new ArrayList<>();
try {
conn = DbUtils.getConnection();
String sql = "select * from employee limit ?,10";
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1,(page-1)*10);
rs = pstmt.executeQuery();
while (rs.next()){
Integer eno = rs.getInt("eno");
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
//JDBC 获取日期使用java.sql.Date,其继承java.util.Date
Date hiredate = rs.getDate("hiredate");
Employee emp = new Employee();
emp.setEno(eno);
emp.setEname(ename);
emp.setSalary(salary);
emp.setDname(dname);
emp.setHiredate(hiredate);
list.add(emp);
}
System.out.println(list.size());
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
DbUtils.closeConnection(rs, pstmt, conn);
}
}
}
JDBC事务管理
JDBC批处理
package com.pro.jdbc.sample;
import com.pro.jdbc.common.DbUtils;
import java.sql.Connection;
import java.util.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* JDBC 批处理
*/
public class BatchSample {
//标准方式插入若干数据
private static void tc1(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
long startTime = new Date().getTime();
conn = DbUtils.getConnection();
//JDBC默认使用自动提交模式,一条语句执行一次
conn.setAutoCommit(false);//关闭自动提交后,需要commit手动提交
String sql = "insert into employee(eno,ename,salary,dname)values(?,?,?,?)";
for (int i=100000;i<200000;i++){
pstmt = conn.prepareStatement(sql);
pstmt.setInt(1, i);
pstmt.setString(2, "员工" +i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.executeUpdate();//执行100000次
}
conn.commit();//手动提交数据
long endTime = new Date().getTime();
System.out.println("tc1()执行时长:"+(endTime-startTime));
} catch (Exception e) {
e.printStackTrace();
try {
if (conn!=null && !conn.isClosed()){
conn.rollback();//回滚数据,如果发生异常,处理不会写入数据库中
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
//标准方式插入若干数据
private static void tc2(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
long startTime = new Date().getTime();
conn = DbUtils.getConnection();
//JDBC默认使用自动提交模式
conn.setAutoCommit(false);//关闭自动提交
String sql = "insert into employee(eno,ename,salary,dname)values(?,?,?,?)";
pstmt = conn.prepareStatement(sql);
for (int i=200000;i<300000;i++){
pstmt.setInt(1, i);
pstmt.setString(2, "员工" +i);
pstmt.setFloat(3, 4000f);
pstmt.setString(4, "市场部");
pstmt.addBatch();//将参数加入批处理任务中
// pstmt.executeUpdate();
}
pstmt.executeBatch();//执行批处理任务,执行一次
conn.commit();//手动提交数据
long endTime = new Date().getTime();
System.out.println("tc2()执行时长:"+(endTime-startTime));
} catch (Exception e) {
e.printStackTrace();
try {
if (conn!=null && !conn.isClosed()){
conn.rollback();//回滚数据
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
} finally {
DbUtils.closeConnection(null,pstmt,conn);
}
}
public static void main(String[] args) {
//对比两种执行方式的处理时间
tc1();
tc2();
}
}
连接池的使用
Druid 连接池配置与使用
配置文件
diverClassName =com.mysql.cj.jdbc.Driver
url =jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true
username =root
password =123456
initialSize =20
maxActive =20
实现类
package com.pro.jdbc.sample;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.pro.jdbc.common.DbUtils;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
/**
* Druid连接池配置与使用
*/
public class DruidSample {
public static void main(String[] args) {
//1.加载属性文件
Properties properties = new Properties();
String propertyFile = DruidSample.class.getResource("/druid-config.properties").getPath();
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
} catch (Exception e) {
e.printStackTrace();
}
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
//2.获取DataSource数据源对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//3.创建数据库连接
conn = dataSource.getConnection();
pstmt = conn.prepareStatement("select * from employee limit 0,10");
rs = pstmt.executeQuery();
while (rs.next()){
Integer eno = rs.getInt(1);//JDBC中的字段索引从1开始,而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname+"-"+eno+"-"+ename+"-"+salary);
}
} catch (Exception e) {
e.printStackTrace();
}finally {
/**
* 不使用连接池:conn.close()关闭连接
* 使用连接池:conn.close()将连接回收至连接池
*/
DbUtils.closeConnection(rs,pstmt,conn);
}
}
}
C3P0连接池配置与使用
配置XML文件注意文件的统一性(c3p0-config.xml)
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<default-config>
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/employee?useSSL=false&Unicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true</property>
<property name="user">root</property>
<property name="password">123456</property>
<!--连接池初始连接数量-->
<property name="initialPoolSize">20</property>
<!--最大连接数量-->
<property name="maxPoolSize">20</property>
</default-config>
</c3p0-config>
实现类
package com.pro.jdbc.sample;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.pro.jdbc.common.DbUtils;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class C3P0Sample {
public static void main(String[] args) {
//1.加载配置文件
//2.创建DataSource
DataSource dataSource = new ComboPooledDataSource();
//3.得到数据库连接
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement("select * from employee limit 0,10");
rs = pstmt.executeQuery();
while (rs.next()){
Integer eno = rs.getInt(1);//JDBC中的字段索引从1开始,而非0
String ename = rs.getString("ename");
Float salary = rs.getFloat("salary");
String dname = rs.getString("dname");
System.out.println(dname+"-"+eno+"-"+ename+"-"+salary);
}
} catch (Exception throwables) {
throwables.printStackTrace();
} finally {
DbUtils.closeConnection(rs, pstmt, conn);
}
}
}
Apache Commons DBUtils
package com.pro.jdbc.sample;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.pro.jdbc.hrapp.entity.Employee;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.net.URLDecoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Properties;
/**
* Apache DBUtils + Druid 联合使用
*/
public class DbUtilsSample {
public static void query() {
Properties properties = new Properties();
String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//利用Apache Dbutils大幅度简化了数据的提取过程
QueryRunner qr = new QueryRunner(dataSource);//获取读取或者写入的数据库
List<Employee> list = qr.query("select *from employee limit ?,10",
new BeanListHandler<>(Employee.class),new Object[]{10});//sql语句,封装JavaBean对象,Object参数数组
for(Employee e:list){
System.out.println(e.getEname());
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void update() {
Properties properties = new Properties();
String propertyFile = DbUtilsSample.class.getResource("/druid-config.properties").getPath();
Connection conn = null;
try {
propertyFile = new URLDecoder().decode(propertyFile, "UTF-8");
properties.load(new FileInputStream(propertyFile));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
conn = dataSource.getConnection();
conn.setAutoCommit(false);
String sql1 = "update employee set salary=salary+1000 where eno=?";
String sql2 = "update employee set salary=salary-1000 where eno=?";
QueryRunner qr = new QueryRunner();
qr.update(conn, sql1, new Object[]{1000});
qr.update(conn, sql2, new Object[]{1001});
conn.commit();
} catch (Exception e) {
e.printStackTrace();
try {
if (conn != null && conn.isClosed()) {
conn.rollback();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}finally {
try {
if (conn != null && conn.isClosed()) {
conn.close();
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void main(String[] args) {
// query();
update();
}
}