JDBC步骤
1. 基础版
-
导包（jdbc-jar包 mysql5选择mysql-connector-java-5.1.44.jar都可以 mysql8选择8版本）
-
注册驱动 Class.forName(“com.mysql.jdbc.Driver”);
-
获取连接对象Connection conn=DriverManager(“url”,“username”,“password”);
-
定义sql语句 String sql=“select * from 表名”；
-
获取执行sql的statement对象Statement stmt=conn.CreatStatement();
-
执行sql语句
- int i=stmt.executeUpdate(sql)----适合增删改
- ResultSet rs=stmt.executeQuery(sql)–适合查询
-
处理结果
-
关闭资源 后开的先关 rs、stmt、conn
package test; import org.junit.Test; import pojo.Account; import java.sql.*; import java.util.ArrayList; import java.util.List; public class accountTest { //查询acount账户表数据，封装为account对象，并且存储到ArrayList集合中 @Test public void TestAccount() throws Exception { //注册驱动 Class.forName("com.mysql.jdbc.Driver"); //获取链接 Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/test?useSSL=false", "root", ""); //定义sql语句 String sql="select * from account"; //获取执行对象 Statement stmt =conn.createStatement(); //执行sql语句 ResultSet rs = stmt.executeQuery(sql); //结果处理 List<Account> list=new ArrayList<>(); while (rs.next()){ String name = rs.getString("name"); Date brith = rs.getDate("brith"); int money = rs.getInt("money"); Account account = new Account(); account.setName(name); account.setBrith(brith); account.setMoney(money); list.add(account); } System.out.println(list); //关闭资源 stmt.close(); conn.close(); } }
2. druid数据库
package test;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;
public class druidTest {
//数据库连接池测试
@Test
public void druidTest01() throws Exception {
//导入druid jar包
//定义配置文件
Properties pop = new Properties();
//加载配置文件
pop.load(new FileInputStream("druid.properties"));
//获取数据库链接对象
DataSource ds = DruidDataSourceFactory.createDataSource(pop);
//获取链接
Connection conn = ds.getConnection();
//定义sql语句
String sql1="select a.money from account a where name=?";
//获取执行sql的statement
PreparedStatement pstmt = conn.prepareStatement(sql1);
pstmt.setString(1,"张三");
//执行sql
ResultSet resultSet = pstmt.executeQuery();
//结果处理
//关闭资源
resultSet.close();
pstmt.close();
conn.close();
/*System.out.println(System.getProperty("user.dir"));*/
}
}
3. 自定义工具类
-
DButil:
package com.lty.util; import java.sql.*; public class DBUtil { static { try { Class.forName("com.mysql.jdbc.Driver"); } catch (ClassNotFoundException e) { e.printStackTrace(); } } private static String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false"; private static String username="lty"; private static String password="？"; //获取连接对象 public static Connection getConnection(){ //注册驱动 //获取数据库连接 Connection conn =null; try { conn=DriverManager.getConnection(url, username, password); } catch (SQLException e) { e.printStackTrace(); } return conn; } //关闭资源 public static void closeAll(ResultSet rs, Statement stmt,Connection conn){ try { if (rs!=null){ rs.close(); rs=null; } if (stmt!=null){ stmt.close(); stmt=null; } if (conn!=null){ conn.close(); conn=null; } } catch (SQLException e) { e.printStackTrace(); } } } -
entity.user实体类
package com.lty.entity; public class User { private int userId; private String username; private String password; private String address; private String phone; public User() { } public User(int userId, String username, String password, String address, String phone) { this.userId = userId; this.username = username; this.password = password; this.address = address; this.phone = phone; } public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } public String getPhone() { return phone; } public void setPhone(String phone) { this.phone = phone; } @Override public String toString() { return "User{" + "userId=" + userId + ", username='" + username + '\'' + ", password='" + password + '\'' + ", address='" + address + '\'' + ", phone='" + phone + '\'' + '}'; } } -
com.lty.IuserDao 接口
package com.lty.dao; import com.lty.entity.User; import java.util.List; public interface IUserDao { /** * 查询所有 * @return */ List<User> selectAll(); /** * 分页查询 * @param cp 当前页数 * @param ps 每页的最大条目数 * @return 返回一个user类型的list集合 */ List<User> selectLimit(int cp,int ps); /** * 根据id查询一条记录 * @param userId * @return */ User selectById(int userId); /** * 添加一条数据 * @param user * @return */ int add(User user); /** * 修改某人数据 * @param user * @return */ int updateById(User user); /** * 根据某人id删除此人 * @param userid * @return */ int deleteById(int userid); } -
UserDaoImpl实现类
package com.lty.dao.impl; import com.lty.dao.IUserDao; import com.lty.entity.User; import com.lty.util.DruidDataSourceUtil; import org.apache.commons.dbutils.QueryRunner; import org.apache.commons.dbutils.handlers.BeanHandler; import org.apache.commons.dbutils.handlers.BeanListHandler; import java.sql.SQLException; import java.util.List; public class UserDaoImpl implements IUserDao { private QueryRunner qr=new QueryRunner(DruidDataSourceUtil.getDataSource()); @Override public int add(User user) { try { return qr.update("insert into user(username,password,address,phone) " + "values(?,?,?,?)",user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone()); } catch (SQLException e) { e.printStackTrace(); } return 0; } @Override public int updateById(User user) { try { return qr.update("update user set username=?,password=?,address=?,phone=? where userId=? ", user.getUsername(),user.getPassword(),user.getAddress(),user.getPhone(),user.getUserId()); } catch (SQLException e) { e.printStackTrace(); } return 0; } @Override public int deleteById(int userid) { try { return qr.update("delete from user where userId=?",userid); } catch (SQLException e) { e.printStackTrace(); } return 0; } } -
测试类
package com.lty.test; import com.lty.dao.IUserDao; import com.lty.dao.impl.UserDaoImpl; import com.lty.entity.User; import org.junit.Test; import java.util.List; public class TestUser { //查询所有 @Test public void testSelectAll(){ UserDaoImpl userDao = new UserDaoImpl(); List<User> users = userDao.selectAll(); for (User ss : users) { System.out.println(ss); } } //分页查询 @Test public void TestSelectByLimit(){ UserDaoImpl userDao = new UserDaoImpl(); List<User> users = userDao.selectLimit(1, 5); for (User ss : users) { System.out.println(ss); } } //根据id查询 @Test public void TestSelectById(){ UserDaoImpl userDao = new UserDaoImpl(); System.out.println( userDao.selectById(1)); } //增加数据 @Test public void TestAdd(){ IUserDao userDao = new UserDaoImpl(); User user = new User(0,"小落","？","富人区","13336894561"); System.out.println(userDao.add(user) > 0 ? "success" : "failed"); } //修改数据 @Test public void TestUpdate(){ IUserDao userDao = new UserDaoImpl(); User user = new User(9,"小落","111111","富人区","13336894561"); System.out.println(userDao.updateById(user) > 0 ? "success" : "failed"); } //删除数据 @Test public void TestDelete(){ IUserDao userDao = new UserDaoImpl(); System.out.println(userDao.deleteById(99) > 0 ? "success" : "failed"); } }
4. 自定义工具类升级版
-
db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/test?serverTimezone=UTC&characterEncoding=UTF-8 user=root pass= -
Env类
package com.lty.util; import java.io.IOException; import java.util.Properties; public class Env extends Properties { private static Env instance=null; private Env(){ //加载properties文件 try { load(Env.class.getResourceAsStream("/db.properties")); } catch (IOException e) { e.printStackTrace(); } } public static Env getInstance(){ if (instance==null){ instance=new Env(); } return instance; } } -
DruidDataSourceUtil:
package com.lty.util; import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.pool.DruidDataSourceFactory; import javax.sql.DataSource; import java.io.IOException; import java.util.Properties; public class DruidDataSourceUtil { private static DruidDataSource dataSource=null; static { Properties p=new Properties(); //加载配置文件 try { p.load(DruidDataSourceUtil.class.getResourceAsStream("/db.properties")); //获取数据库连接对象 dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(p); //设置四个参数 dataSource.setUrl(Env.getInstance().getProperty("url")); dataSource.setDriverClassName(Env.getInstance().getProperty("driver")); dataSource.setUsername(Env.getInstance().getProperty("user")); dataSource.setPassword(Env.getInstance().getProperty("pass")); } catch (IOException e) { e.printStackTrace(); } catch (Exception e) { e.printStackTrace(); } } public static DataSource getDataSource(){ return dataSource; } } -
AllUtil：
package com.lty.util; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; public class AllUtil<T> { private static Connection conn = null; private static PreparedStatement pstmt = null; private static ResultSet rs = null; //增删改通用 public static int commonUpdate(String sql, Object... params) { //获取connection对象 conn = DBUtil.getConnection(); //获取执行sql的preperstatement try { pstmt = conn.prepareStatement(sql); //给sql语句设置？的值 for (int i = 0; i < params.length; i++) { Object param = params[i]; pstmt.setObject((i + 1), param); } //执行sql语句 int i = pstmt.executeUpdate(); return i; } catch (SQLException e) { e.printStackTrace(); } finally { //释放资源 DBUtil.closeAll(rs, pstmt, conn); } return 0; } //查询通用 public List<T> commomSelect(String sql, Rowmapper<T> rowmapper, Object... params) { List<T> list = null; //获取连接 conn = DBUtil.getConnection(); //获取执行sql的对象 try { pstmt = conn.prepareStatement(sql); //设置参数 for (int i = 0; i < params.length; i++) { Object param = params[i]; pstmt.setObject((i + 1), param); } //执行sql rs = pstmt.executeQuery(); if (rs != null) { list = new ArrayList<>(); while (rs.next()) { T t = rowmapper.getRow(rs); list.add(t); } } } catch (SQLException e) { e.printStackTrace(); } finally { //关闭资源 DBUtil.closeAll(rs, pstmt, conn); } return list; } } -
Rowmapper接口
package com.lty.util; import java.sql.ResultSet; public interface Rowmapper<T> { T getRow(ResultSet rs); } -
RowMapperImpl:
package com.lty.dao.impl; import com.lty.entity.Student; import com.lty.util.Rowmapper; import java.sql.ResultSet; import java.sql.SQLException; public class RowMapperImpl implements Rowmapper<Student> { @Override public Student getRow(ResultSet rs) { try { Student stu = new Student(rs.getInt(1), rs.getString(2), rs.getInt(3 ), rs.getDouble(4)); return stu; } catch (SQLException e) { e.printStackTrace(); } return null; } } -
student相关：
package com.lty.entity; public class Student { private int sid; private String name; private int age; private double score; public Student() { } public Student(int sid, String name, int age, double score) { this.sid = sid; this.name = name; this.age = age; this.score = score; } public int getSid() { return sid; } public void setSid(int sid) { this.sid = sid; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public double getScore() { return score; } public void setScore(double score) { this.score = score; } @Override public String toString() { return "Student{" + "sid=" + sid + ", name='" + name + '\'' + ", age=" + age + ", score=" + score + '}'; } }package com.lty.dao; import com.lty.entity.Student; import java.util.List; public interface IStudentDao { /** * 查询所有 * @return */ List<Student> selectAll(); /** * 分页查询 * @param cp * @param ps * @return */ List<Student> selectLimit(int cp,int ps); /** * 根据id查询 * @param sid * @return */ Student selectBySid(int sid); /** * 增加学生 * @param student * @return */ int insertStudent(Student student); /** * 修改学生 * @param student * @return */ int updateStudent(Student student); /** * 删除学生 * @return */ int deleteStudent(); }
package com.lty.dao.impl;
import com.lty.dao.IStudentDao;
import com.lty.entity.Student;
import com.lty.util.DBUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class StudentDaoImpl implements IStudentDao {
private Connection conn = null;
private Statement stmt = null;
private ResultSet rs = null;
private List<Student> list = null;
@Override
public List<Student> selectAll() {
Student student=null;
//获取数据库连接对象
conn = DBUtil.getConnection();
//定义sql语句
String sql = "select * from student";
//获取执行sql的preperstatement对象
try {
stmt = conn.createStatement();
//执行sql
rs = stmt.executeQuery(sql);
if (rs != null) {
list = new ArrayList<>();
while (rs.next()) {
student=new Student(rs.getInt(1),rs.getString(2),rs.getInt(3),
rs.getDouble(4) );
list.add(student);
}
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//关闭资源
DBUtil.closeAll(rs, stmt,conn);
}
return list;
}
@Override
public List<Student> selectLimit(int cp, int ps) {
return null;
}
@Override
public Student selectBySid(int sid) {
return null;
}
@Override
public int insertStudent(Student student) {
return 0;
}
@Override
public int updateStudent(Student student) {
return 0;
}
@Override
public int deleteStudent() {
return 0;
}
}
转账小系统
package com.lty.entity;
public class Account {
private String aid;
private String aname;
private String password;
private double money;
public Account() {
}
public Account(String aid, String aname, String password, double money) {
this.aid = aid;
this.aname = aname;
this.password = password;
this.money = money;
}
public String getAid() {
return aid;
}
public void setAid(String aid) {
this.aid = aid;
}
public String getAname() {
return aname;
}
public void setAname(String aname) {
this.aname = aname;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public double getMoney() {
return money;
}
public void setMoney(double money) {
this.money = money;
}
@Override
public String toString() {
return "Account{" +
"aid='" + aid + '\'' +
", aname='" + aname + '\'' +
", password='" + password + '\'' +
", money=" + money +
'}';
}
}
package com.lty.dao;
import com.lty.entity.User;
import java.util.List;
public interface IAccountDao {
/**
* 转出金额
* @param aid
* @param password
* @param money
* @return
*/
int cutAccount(String aid,String password,double money);
/**
* 转入金额
* @param aid
* @param password
* @param money
* @return
*/
int saveAccount(String aid,double money);
boolean transfer(String froid,String froPassword,String toid,double money);
}
package com.lty.dao.impl;
import com.lty.dao.IAccountDao;
import com.lty.util.AllUtil;
public class AccountDaoImpl implements IAccountDao {
@Override
public int cutAccount(String aid, String password, double money) {
return AllUtil.commonUpdate("update account set money=money-? where " +
"aid=? and password=?", money,aid,password);
}
@Override
public int saveAccount(String aid, double money) {
return AllUtil.commonUpdate("update account set money=money+? where aid=?", money,aid);
}
@Override
public boolean transfer(String froid, String froPassword, String toid, double money) {
int i = cutAccount(froid, froPassword, money);
int i1 = saveAccount(toid, money);
if (i>0&&i1>0){
return true;
}
return false;
}
}
package com.lty.servive;
public interface IAccountService {
boolean transfer(String froid,String froPassword,String toid,double money);
}
package com.lty.servive.impl;
import com.lty.dao.IAccountDao;
import com.lty.dao.impl.AccountDaoImpl;
import com.lty.servive.IAccountService;
import com.lty.util.DBUtil;
import java.sql.Connection;
import java.sql.SQLException;
public class AccountServiceimpl implements IAccountService {
@Override
public boolean transfer(String froid, String froPassword, String toid, double money) {
IAccountDao acc = new AccountDaoImpl();
Connection conn = DBUtil.getConnection();
boolean flag = false;
try {
conn.setAutoCommit(false);
int i = acc.cutAccount(froid, froPassword, money);
int i1 = acc.saveAccount(toid, money);
if (i>0&&i1>0){
conn.commit();
flag=true;
}else {
conn.rollback();
flag=false;
}
} catch (Exception e) {
e.printStackTrace();
}
return flag;
}
}
测试类：
package com.lty.test;
import com.lty.servive.IAccountService;
import com.lty.servive.impl.AccountServiceimpl;
import java.util.Scanner;
public class TestAccountService {
public static void main(String[] args) {
IAccountService a=new AccountServiceimpl();
Scanner sc=new Scanner(System.in);
System.out.println("请输入转账卡号：");
String froid = sc.next();
System.out.println("请输入转账人密码");
String fropassword = sc.next();
System.out.println("收款卡号：");
String toid = sc.next();
System.out.println("转账金额：");
double money = sc.nextDouble();
System.out.println(a.transfer(froid, fropassword, toid, money) ? "success" : "false");
}
}
转账前
转账后：
扫一扫
1824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
