JDBC组件
DriverManager：管理一些系列数据库的驱动程序
核心对象
Connection：实现数据库与应用程序之间的联通 Statement：执行SQL命令，写（增删改），读（查） ResultSet：接收查询返回的结果
Jar包
下载地址
我的数据库是8.0，我下载的jar包mysql-connector-java-8.0.21.jar
创建项目
添加目录，更改为源目录
加入jar包
复制jar包到创建好的源目录
JDBC连接数据库
版本
public static void main(String[] args) throws Exception {
String url = "jdbc:mysql://localhost:3306/j0705?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "root";
String sql = "insert into employee values(default,'折腾的小飞','男',100,8900);";
// 加载驱动，反射核心类的forName方法
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
Connection con = DriverManager.getConnection(url, user, password);
// 执行SQL
Statement stmt = con.createStatement();
// stmt.executeQuery() 查
int n = stmt.executeUpdate(sql); // 增删改
System.out.println("所受影响的行数：" + n);
// 处理执行结果
}- 无条件查询，返回结果
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String url = "jdbc:mysql://localhost:3306/j0705?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "root";
String selectSql = "select * from employee;";
// 加载驱动，反射核心类的forName方法
Class.forName("com.mysql.cj.jdbc.Driver");
// 建立连接
Connection con = DriverManager.getConnection(url, user, password);
// 执行SQL
Statement stmt = con.createStatement();
ResultSet resultSet = stmt.executeQuery(selectSql);
StringBuffer sb = new StringBuffer("");
while (resultSet.next()) {
sb.append(resultSet.getInt("enumber")).append("\t");
sb.append(resultSet.getString("ename")).append("\t");
sb.append(resultSet.getString("sex")).append("\t");
sb.append(resultSet.getInt("pnumber")).append("\t");
sb.append(resultSet.getString("salary"));
sb.append("\r\n");
}
System.out.println(sb.toString());
}
- 条件查询，返回结果
static String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
static String user = "root";
static String password = "123456";
static String driver = "com.mysql.jdbc.Driver";
public static void main(String[] args) throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url, user, password);
String sql = "select * from Product where id=?";
Object[] parms = {1};
PreparedStatement ps = con.prepareStatement(sql);
for (int i = 0; i < parms.length; i++) {
ps.setObject(i + 1, parms[i]);
}
ResultSet resultSet = ps.executeQuery();
int id = -1;
String ProductName = "";
int Inventory = 0;
Map<Integer, String> map = new HashMap<>();
if (resultSet.next()) {
id = resultSet.getInt("id");
ProductName = resultSet.getString("ProductName");
Inventory = resultSet.getInt("Inventory");
map.put(id, ProductName + "," + Inventory);
}
for (Integer key : map.keySet()) {
System.out.println("商品名称："+map.get(key).split(",")[0]);
System.out.println("库存数量："+map.get(key).split(",")[1]);
}
}
- 简单的登陆验证
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/j0705?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
String user = "root";
String password = "root";
Connection con = DriverManager.getConnection(url, user, password);
Statement statement = con.createStatement();
System.out.println("等待输入用户名：");
Scanner scanner = new Scanner(System.in);
String inUser = scanner.nextLine();
System.out.println("请输入密码：");
String inPassword = scanner.nextLine();
String selectSqlUser = "select `password` from users where `user` = '" + inUser + "';";
ResultSet resultSet = statement.executeQuery(selectSqlUser);
String pwd = "";
while (resultSet.next()) {
pwd = resultSet.getString("password");
}
if (inPassword.equals(pwd)) {
System.out.println("登陆成功！！！");
} else {
System.out.println("密码不正确！");
}
}
创建JDBCUtils工具类
package com.mysqljdbc.Utils;
import com.mysqljdbc.pojo.OutParameter;
import java.sql.*;
import java.util.List;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: TODO 类描述
* @create 2021-08-30 9:21
*/
public class JDBCUtils {
static String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
static String user = "root";
static String password = "123456";
static String driver = "com.mysql.jdbc.Driver";
public static ResultSet query(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
return rs;
}
public static int update(String sql, Object[] params) throws Exception {
int r = -1;
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
r = ps.executeUpdate();
return r;
}
// 存储过程,输入参数
public static ResultSet procQuery(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
CallableStatement callstmt = con.prepareCall(sql);
if (params!=null){
for (int i = 0; i < params.length; i++) {
callstmt.setObject(i + 1, params[i]);
}
}
ResultSet r = callstmt.executeQuery();
return r;
}
//存储过程，非查询，输入参数
public static int procUpdate(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
CallableStatement callstmt = con.prepareCall(sql);
if (params!=null){
for (int i = 0; i < params.length; i++) {
callstmt.setObject(i + 1, params[i]);
}
}
int r = callstmt.executeUpdate();
return r;
}
public static void parameterProc(String sql, Object[] inparams, List<OutParameter> outparams) throws Exception{
Class.forName(driver);
Connection con=DriverManager.getConnection(url,user,password);
CallableStatement callstmt=con.prepareCall(sql);
if (inparams!=null){
for (int i = 0; i < inparams.length; i++) {
callstmt.setObject(i+1,inparams[i]);
}
}
if (outparams!=null){
for (OutParameter outparam : outparams) {
callstmt.registerOutParameter(outparam.getpName(),outparam.getpType());
}
}
callstmt.executeUpdate();
for (OutParameter outparam : outparams) {
outparam.setpValue(callstmt.getObject(outparam.getpName()));
}
}
}- 执行查询
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.sql.ResultSet;
import java.util.Scanner;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 查询
* @create 2021-08-30 9:33
*/
public class Test1 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String user = sc.nextLine();
System.out.println("请输入密码：");
String pwd = sc.nextLine();
String selectSql = "select * from user where `user`=? and password=?";
Object[] pars = {user, pwd};
StringBuffer sbf = new StringBuffer();
try {
ResultSet query = JDBCUtils.query(selectSql, pars);
while (query.next()) {
sbf.append(query.getInt("id")).append("\t");
sbf.append(query.getString("user")).append("\t");
sbf.append(query.getString("password"));
sbf.append("\r\n");
}
} catch (Exception e) {
e.printStackTrace();
}
System.out.println(sbf);
}
}
- 执行插入数据
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.util.Scanner;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 插入 类描述
* @create 2021-08-30 9:45
*/
public class Test2 {
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
String updatesql = "";
System.out.println("请输入要注册的用户名：");
String user = sc.nextLine();
System.out.println("请输入密码：");
String pwd1 = sc.nextLine();
System.out.println("请再次输入密码：");
String pwd2 = sc.nextLine();
Object[] pars = {user, pwd1};
if (pwd1.equals(pwd2)) {
updatesql = "insert into user values(default,?,?)";
try {
int i = JDBCUtils.update(updatesql, pars);
if (i != 0) {
System.out.println("修改成功," + i + "条数据已修改！");
} else {
System.out.println("修改失败");
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
- 防止SQL注入,验证用户登录
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 登录验证 类描述
* @create 2021-08-30 10:48
*/
public class Test4 {
static String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
static String users = "root";
static String password = "123456";
static String driver = "com.mysql.jdbc.Driver";
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String user = sc.nextLine();
System.out.println("请输入密码：");
String pwd = sc.nextLine();
String sql = "select * from user where `user`= ? and `password`= ?";
// String sql = "select * from user where `user`='" + user + "' and `password`= '" + pwd + "'";
// String sql = "select * from user where `user`='admin' or 1=1 # and `password`= '" + pwd + "'";
//注册 参数
Object[] params = {user, pwd};
Class.forName(driver);
Connection con=DriverManager.getConnection(url, users,password);
Statement stmt=con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
}
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.sql.*;
import java.util.Scanner;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 登录验证 类描述
* @create 2021-08-30 10:48
*/
public class Test4 {
public static void main(String[] args) throws Exception {
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名：");
String user = sc.nextLine();
System.out.println("请输入密码：");
String pwd = sc.nextLine();
String sql = "select * from user where `user`= ? and `password`= ?";
//注册 参数
Object[] params = {user, pwd};
ResultSet rs = JDBCUtils.procQuery(sql, params);
if (rs.next()) {
System.out.println("登录成功");
} else {
System.out.println("登录失败");
}
}
}
- 带参数的存储过程-输入参数
CREATE DEFINER=`root`@`localhost` PROCEDURE `JDBCProc`(stu varchar(30))
BEGIN
select * from student where `name`=stu;
END
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.sql.*;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 执行带参数的存储过程 类描述
* @create 2021-08-30 11:35
*/
public class Test5 {
public static void main(String[] args) throws Exception {
String procsql = "{call `JDBCProc`(?)}";
Object[] pars = {"任我行"};
ResultSet proc = JDBCUtils.procQuery(procsql, pars);
if (proc.next()){
System.out.print(proc.getInt("id")+"\t"+proc.getString("name")+"\t"+proc.getString("major"));
}
}
}
- 带输入输出参数的存储过程，main方法
CREATE DEFINER=`root`@`localhost` PROCEDURE `intoProc`(
in sid int,
out `sname` varchar(20),
out `total` int
)
BEGIN
select stu.`name` into sname from student stu where stu.id=sid;
select sum(result) into total from score sc where sc.sid=sid
group by sc.sid;
END
package com.mysqljdbc.test;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.Collection;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 执行带输入输出参数的存储过程，全在main方法 类描述
* @create 2021-08-30 13:41
*/
public class Test6 {
static String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
static String user = "root";
static String password = "123456";
static String driver = "com.mysql.jdbc.Driver";
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName(driver);
Connection con = DriverManager.getConnection(url, user, password);
int sid = 2;
String sname = "";
int total = 0;
String sql = "{call `intoProc`(?,?,?)}";
CallableStatement callstmt = con.prepareCall(sql);
callstmt.setInt(1, sid);
callstmt.registerOutParameter("sname", Types.VARCHAR);
callstmt.registerOutParameter("total", Types.INTEGER);
callstmt.executeUpdate();
sname = callstmt.getString("sname");
total = callstmt.getInt("total");
System.out.println("姓名：" + sname + "\t" + "总分：" + total);
}
}
- 带输入输出参数的存储过程，工具类
package com.mysqljdbc.pojo;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: MySQL实体类 类描述
* @create 2021-08-30 15:13
*/
public class OutParameter {
private String pName;
private int pType;
private Object pValue; //返回值
public OutParameter() {
}
public OutParameter(String pName, int pType, Object pValue) {
this.pName = pName;
this.pType = pType;
this.pValue = pValue;
}
public String getpName() {
return pName;
}
public void setpName(String pName) {
this.pName = pName;
}
public int getpType() {
return pType;
}
public void setpType(int pType) {
this.pType = pType;
}
public Object getpValue() {
return pValue;
}
public void setpValue(Object pValue) {
this.pValue = pValue;
}
}
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import com.mysqljdbc.pojo.OutParameter;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 执行带输入输出参数的存储过程，封装在工具类中 类描述
* @create 2021-08-30 15:03
*/
public class Test7 {
public static void main(String[] args) throws Exception {
String sql="{call `intoProc`(?,?,?)}";
Object[] inparms={2};
List<OutParameter> list=new ArrayList<>();
list.add(new OutParameter("sname", Types.VARCHAR,null));
list.add(new OutParameter("total", Types.INTEGER,null));
JDBCUtils.parameterProc(sql,inparms,list);
System.out.println(list.get(0).getpValue());
System.out.println(list.get(1).getpValue());
}
}- 聚合查询（单行记录）
static String url = "jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC";
static String user = "root";
static String password = "123456";
static String driver = "com.mysql.jdbc.Driver";
// 执行聚合查询（单行记录）
public static List<Object> scalar(String sql,Object[] params) throws Exception{
Class.forName(driver);
Connection con=DriverManager.getConnection(url,user,password);
PreparedStatement ps=con.prepareStatement(sql);
if (params!=null){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
}
ResultSet rs = ps.executeQuery();
List<Object> list=new ArrayList<>();
if (rs.next()){
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 0; i < columnCount; i++) {
list.add(rs.getObject(i+1));
}
}
return list;
}
package com.mysqljdbc.test;
import com.mysqljdbc.Utils.JDBCUtils;
import java.util.ArrayList;
import java.util.List;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: TODO 类描述
* @create 2021-08-30 16:45
*/
public class Test8 {
public static void main(String[] args) {
String sql="select min(result),max(result),avg(result) from score where course=?";
Object[] prams={"HTML"};
try {
List<Object> list=JDBCUtils.scalar(sql,prams);
System.out.println("最低分："+list.get(0));
System.out.println("最高分："+list.get(1));
System.out.println("平均分："+list.get(2));
} catch (Exception e) {
e.printStackTrace();
}
}
}
数据库连接池
配置文件properties
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/demo?characterEncoding=utf8&useSSL=false&serverTimezone=UTC
username=root
password=123456druid-1.1.10数据库连接池
Jar包下载地址
Maven Repository: com.alibaba » druid » 1.1.10 (mvnrepository.com)
GitHub源码
package com.mysqljdbc.Utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.util.Properties;
public class DruidUtils {
private static DataSource ds;
static {
try {
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
}
catch (Exception ex){
ds = null;
}
}
public static DataSource getDataSource(){
return ds;
}
}
public static void main(String[] args) throws SQLException, ClassNotFoundException {
DataSource ds= DruidUtils.getDataSource();
Connection con=ds.getConnection();
String sql = "select * from student where major=?";
Object[] parms={"计算机应用"};
PreparedStatement ps=con.prepareStatement(sql);
ps.setObject(1,parms[0]);
ResultSet resultSet = ps.executeQuery();
while (resultSet.next()){
System.out.println(resultSet.getString("name"));;
}
}
使用QueryRunner处理返回数据
commons-dbutils-1.6.jarhttps://raw.githubusercontent.com/731016/imgSave/master/note_img202110281819062.png
使用数据库连接池和QueryRunner处理，带参数的查询
返回的每一行结果，使用对象存储，放到List集合
public static void main(String[] args) throws Exception {
DataSource ds= DruidUtils.getDataSource();
QueryRunner runner = new QueryRunner(ds);
BeanListHandler<Student> handler = new BeanListHandler(Student.class);
String sql = "select * from student where major=?";
Object[] parms={"计算机应用"};
List<Student> studentList = runner.query(sql, handler,parms);
for (Student student : studentList) {
System.out.println(student);
}
}
- 带单个聚合函数的查询
public interface EmployeeService {
// (8)根据部门名称，查询该部门员工的平均工资：public int getAvgSalary(String dName);
public int getAvgSalary(String dName);
}
public int getAvgSalary(String dName) {
try {
QueryRunner runner = new QueryRunner(ds);
ScalarHandler<Double> handler = new ScalarHandler();
String conditionQuery = "select avg(salary) from employee e inner join department d on e.pid=d.pid where d.pname=?";
Object[] params = new Object[]{
dName
};
Double aDouble = runner.query(conditionQuery, handler, params);
return (int) Math.round(aDouble);
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return 0;
}
public static void main(String[] args) {
try {
EmployeeServiceImpl employeeService = new EmployeeServiceImpl();
System.out.println("平均工资：" + employeeService.getAvgSalary("研发部"));
} catch (Exception e) {
e.printStackTrace();
}
}
QueryRunner
- queryRunner和Test测试类处理返回结果
package com.j0705.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.apache.commons.dbutils.QueryRunner;
import javax.sql.DataSource;
import java.sql.CallableStatement;
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 JDBCUtils {
private static DataSource ds;
static {
try {
//1.加载配置文件
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"));
//2.获取DataSource
ds = DruidDataSourceFactory.createDataSource(pro);
}
catch (Exception e){
ds = null;
}
}
private static Connection getConnection(){
Connection con;
try {
con = ds.getConnection();
}
catch (Exception ex){
con = null;
}
return con;
}
/* 获取 QueryRunner */
public static QueryRunner getQueryRunner(){
QueryRunner runner = new QueryRunner(ds);
return runner;
}
public static CallableStatement getStatement(String sql) throws Exception{
CallableStatement call = getConnection().prepareCall(sql);
return call;
}
/**
* 执行查询 SQL语句
*/
public static ResultSet query(String sql, Object[] params) throws Exception{
ResultSet rs = null;
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql);
//如果sql含有？，则需要在命令对象中注册对应的参数
if (params != null){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
}
rs = ps.executeQuery();
return rs;
}
/**
* 执行增删改SQL语句
*/
public static int update(String sql,Object[] params) throws Exception{
int r = -1;
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql);
//如果sql含有？，则需要在命令对象中注册对应的参数
if (params != null){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
}
r = ps.executeUpdate();
return r;
}
/**
* 执行聚合查询（单行记录）
*/
public static List<Object> scalar(String sql, Object[] params) throws Exception{
List<Object> objects = new ArrayList<>();
Connection con = getConnection();
PreparedStatement ps = con.prepareStatement(sql);
//如果sql含有？，则需要在命令对象中注册对应的参数
if (params != null){
for (int i = 0; i < params.length; i++) {
ps.setObject(i+1,params[i]);
}
}
ResultSet rs = ps.executeQuery();
if (rs.next()){
// 获取记录的总列数：
int cols = rs.getMetaData().getColumnCount();
for (int i=0; i<cols; i++){
objects.add(rs.getObject(i+1));
}
}
return objects;
}
}
package com.j0705.ui;
import com.j0705.pojo.Student;
import com.j0705.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Types;
import java.util.List;
public class TestOne {
private QueryRunner qr;
public TestOne(){
qr = JDBCUtils.getQueryRunner();
}
@Test
public void test01() throws Exception{
// 精确查询，返回一行记录
String sql = "select * from student where sid = ? ";
BeanHandler<Student> handler = new BeanHandler<>(Student.class);
Object[] params = {20};
Student std = qr.query(sql,handler,params);
System.out.println("学号：" + std.getSid());
System.out.println("姓名：" + std.getSname());
System.out.println("专业：" + std.getMajor());
}
@Test
public void test03() throws Exception{
// 聚合查询(1个字段)
String sql = "select count(*) from student";
ScalarHandler<Long> handler = new ScalarHandler<>();
Long count = qr.query(sql,handler);
System.out.println("总人数：" + count);
}
@Test
public void test02() throws Exception{
// 聚合查询(多个字段)
String sql = "select max(result) max,avg(result) avg from score where course='java'";
ArrayHandler handler = new ArrayHandler();
Object[] arr = qr.query(sql,handler);
System.out.println("最高分：" + arr[0]);
System.out.println("平均分：" + arr[1]);
}
@Test
public void test04() throws Exception{
String sql = "{ call pro_output(?,?,?) }";
int sid = 7;
CallableStatement call=JDBCUtils.getStatement(sql);
call.registerOutParameter("sname", Types.VARCHAR);
call.registerOutParameter("total",Types.INTEGER);
call.setObject(1,sid);
call.executeUpdate();
System.out.println(call.getObject(3));
}
@Test
public void test05() throws Exception{
String sql = "{ call pro_student_major(?)}";
Object[] arr = {"土木工程"};
BeanListHandler<Student> handler = new BeanListHandler<>(Student.class);
List<Student> students = qr.query(sql,handler,arr);
for (Student ss : students) {
System.out.println(ss.getSname() + " " + ss.getMajor());
}
}
}三层架构
pojo.实体类 -类utils.JDBCutils -类dao(database access object) -接口 impl.实体类Impl -实现类service.实体类Service -接口 impl.实体类Impl -实现类ui.Test -测试类
结构
- pojo
package com.framework.pojo;
import java.util.Date;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 数据库映射类 类描述
* @create 2021-09-01 10:49
*/
public class Student {
private Integer id;
private String name;
private String gender;
private Date birthday;
private String major;
public Integer getId() {
return id;
}
public void setId(Integer 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;
}
public Date getBirthday() {
return birthday;
}
public void setBirthday(Date birthday) {
this.birthday = birthday;
}
public String getMajor() {
return major;
}
public void setMajor(String major) {
this.major = major;
}
public Student() {
}
public Student(Integer id, String name, String gender, Date birthday, String major) {
this.id = id;
this.name = name;
this.gender = gender;
this.birthday = birthday;
this.major = major;
}
}- dao
package com.framework.dao;
import com.framework.pojo.Student;
import java.sql.SQLException;
public interface StudentDao {
public int add(Student student) throws Exception;
}- impl
package com.framework.dao.impl;
import com.framework.dao.StudentDao;
import com.framework.pojo.Student;
import com.framework.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 数据访问层实现类 类描述
* @create 2021-09-01 11:03
*/
public class StudentDaoImpl implements StudentDao {
@Override
public int add(Student student) throws Exception {
String addSql = "insert into student values(default,?,?,?,?)";
Object[] params={
student.getName(),
student.getGender(),
student.getBirthday(),
student.getMajor()
};
int updatelines = JDBCUtils.update(addSql, params);
return updatelines;
}
}- sercice
package com.framework.service;
import com.framework.pojo.Student;
import java.sql.SQLException;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 业务逻辑层接口 类描述
* @create 2021-09-01 11:17
*/
public interface StudentService {
public int add(Student student) throws Exception;
}- impl
package com.framework.service.impl;
import com.framework.dao.StudentDao;
import com.framework.dao.impl.StudentDaoImpl;
import com.framework.pojo.Student;
import com.framework.service.StudentService;
import java.sql.SQLException;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 业务逻辑层实现类 类描述
* @create 2021-09-01 11:18
*/
public class StudentServiceImpl implements StudentService {
private StudentDao dao;
public StudentServiceImpl() {
dao = new StudentDaoImpl();
}
@Override
public int add(Student student) throws Exception {
int addlines = dao.add(student);
return addlines;
}
}- utils
package com.framework.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.framework.pojo.Student;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 数据库工具类 类描述
* @create 2021-09-01 10:51
*/
public class JDBCUtils {
static DataSource ds;
static {
try {
Properties pro = new Properties();
pro.load(JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties"));
ds = DruidDataSourceFactory.createDataSource(pro);
} catch (Exception e) {
ds = null;
}
}
public static Connection getConnection() {
Connection con;
try {
con = ds.getConnection();
} catch (Exception e) {
con = null;
}
return con;
}
public static ResultSet query(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Connection con = JDBCUtils.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
rs = ps.executeQuery();
return rs;
}
public static int update(String sql, Object[] params) throws Exception {
int r = -1;
Connection con = JDBCUtils.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
r = ps.executeUpdate();
return r;
}
// 执行聚合查询（单行记录）
public static List<Object> scalar(String sql, Object[] params) throws Exception {
Connection con = JDBCUtils.getConnection();
PreparedStatement ps = con.prepareStatement(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
ps.setObject(i + 1, params[i]);
}
}
ResultSet rs = ps.executeQuery();
List<Object> list = new ArrayList<>();
if (rs.next()) {
int columnCount = rs.getMetaData().getColumnCount();
for (int i = 0; i < columnCount; i++) {
list.add(rs.getObject(i + 1));
}
}
return list;
}
// 存储过程,输入参数
public static ResultSet procQuery(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Connection con = JDBCUtils.getConnection();
CallableStatement callstmt = con.prepareCall(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
callstmt.setObject(i + 1, params[i]);
}
}
ResultSet r = callstmt.executeQuery();
return r;
}
//存储过程，非查询，输入参数
public static int procUpdate(String sql, Object[] params) throws Exception {
ResultSet rs = null;
Connection con = JDBCUtils.getConnection();
CallableStatement callstmt = con.prepareCall(sql);
if (params != null) {
for (int i = 0; i < params.length; i++) {
callstmt.setObject(i + 1, params[i]);
}
}
int r = callstmt.executeUpdate();
return r;
}
}- ui
package com.framework.ui;
import com.framework.pojo.Student;
import com.framework.service.StudentService;
import com.framework.service.impl.StudentServiceImpl;
import java.util.Date;
/**
* @author 涂鏊飞tu_aofei@163.com
* @description: 测试类 类描述
* @create 2021-09-01 11:18
*/
public class Test1 {
public static void main(String[] args) {
try {
Date birth = new Date();
Student stu = new Student(null, "大过户", "男", birth, "会计学");
StudentService service = new StudentServiceImpl();
int add = service.add(stu);
System.out.println("修改行数：" + add);
} catch (Exception e) {
e.printStackTrace();
}
}
}允许接收键盘输入
1.IDEA进入help->Edit Custom VM Options
2.添加一行：-Deditable.java.test.console=true
3.重启IDEA
重启步骤：
1）点击File–>Invalidate Caches进入重启窗口
2）选择重启方式，四个按钮，一共三种重启方式：
① Invalidate and Restart 清空缓存并重启。
② Invalidate 清除缓存，下次打开重启。
③ Cancel 取消。
④ Just Restart 重启。
