这次的代码和之前学习到一般的代码主要就是将一些很常见的操作（建立连接、清除连接）不管做什么操作都需要用到它们，所以将它们单独放到另一个工具类里面去。
用到的术语：
1.事务：https://www.cnblogs.com/cstdio1/p/11626657.html
2.缓冲池（数据源）：https://www.cnblogs.com/chy18883701161/p/11374731.html
主逻辑代码：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import JDBCUtils.JDBCUtils;
import JDBCUtils.JDCPDataSource;
public class MysqlDemo1 {
public static void main(String[] args) {
selectAll();
//System.out.println(selectByUsernamePassword2("zs","123"));
//sql注入
//System.out.println(selectByUsernamePassword("zs","12347‘or‘1‘=‘1"));
//PageSearch(1,2);
//insert("sdf","249.1");
//delete("sdf");
//update("zs","123","000");
transAccount("zs","ls",1000);
}
public static void selectAll(){
// TODO Auto-generated method stub
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
con = JDCPDataSource.getConnection();
stmt = con.createStatement();
String SqlRequest = "select * from student";
rs= stmt.executeQuery(SqlRequest);
while(rs.next()){
System.out.println(rs.getString(1) " " rs.getString(2) " " rs.getString(3) " " rs.getString(4));
//System.out.println(rs.getString("id") " " rs.getString("stu_name") " " rs.getString("stu_sex") " " rs.getString("stu_score"));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDCPDataSource.closeResource(rs, stmt, con);
//JDBCUtils.closeResource(rs, stmt, con);
}
}
public static boolean selectByUsernamePassword(String username,String password){//验证用户名和密码（字符串拼接的方式存在sql注入的问题）
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
con = JDBCUtils.getConnection();
stmt = con.createStatement();
String requestSql="select * from user where u_name=‘" username "‘and u_password=‘" password "‘";
System.out.print(requestSql);
rs = stmt.executeQuery(requestSql);
if(rs.next()){
return true;
}else{
return false;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, stmt, con);
}
return false;
}
public static boolean selectByUsernamePassword2(String username,String password){//验证用户名和密码(版本2可以防止sql注入)
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con = JDBCUtils.getConnection();
String RequestSql="select *from user where u_name=? and u_password=? ";
pstmt = con.prepareStatement(RequestSql);
pstmt.setString(1, username);
pstmt.setString(2,password);
rs = pstmt.executeQuery();
if(rs.next()){
return true;
}else{
return false;
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt, con);
}
return false;
}
/*
*PageNum：查询第几页
*LineNum：总共显示多少行
*/
public static void PageSearch(int PageNum,int LineNum){
//分页查询
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con = JDBCUtils.getConnection();
String RequestSql="select *from user limit ?,?";
pstmt = con.prepareStatement(RequestSql);
pstmt.setInt(1,(PageNum-1)*LineNum);
pstmt.setInt(2,LineNum);
rs = pstmt.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1) " " rs.getString(2));
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt, con);
}
}
public static void insert(String UserName,String Password){
//新注册的信息进行插入操作
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
int mark=0;
try {
con = JDBCUtils.getConnection();
String RequestSql="insert into user(u_name,u_password) values(?,?)";
pstmt = con.prepareStatement(RequestSql);
pstmt.setString(1, UserName);
pstmt.setString(2,Password);
mark = pstmt.executeUpdate();
if(mark>0){
System.out.println("插入成功");
}else{
System.out.println("插入失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt, con);
}
}
public static void delete(String UserName){
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
int mark=0;
try {
con = JDBCUtils.getConnection();
String RequestSql="delete from user where u_name = ?";
pstmt = con.prepareStatement(RequestSql);
pstmt.setString(1, UserName);
mark = pstmt.executeUpdate();
if(mark>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt, con);
}
}
public static void update(String UserName,String OldPassword,String NewPassword){
//修改用户密码
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
int mark=0;
try {
con = JDBCUtils.getConnection();
String RequestSql="update user set u_password = ? where u_name = ? and u_password = ? ";
pstmt = con.prepareStatement(RequestSql);
pstmt.setString(1, NewPassword);
pstmt.setString(2, UserName);
pstmt.setString(3, OldPassword);
mark = pstmt.executeUpdate();
if(mark>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt, con);
}
}
public static void transAccount(String UserName1,String UserName2,int money){
//转账操作(利用事务)
Connection con=null;
PreparedStatement pstmt1=null;
PreparedStatement pstmt2=null;
ResultSet rs=null;
try {
con = JDBCUtils.getConnection();
con.setAutoCommit(false);//开启事务
String sql="update user set u_balance = u_balance - ? where u_name = ? ";
pstmt1 = con.prepareStatement(sql);
pstmt1.setInt(1, money);
pstmt1.setString(2, UserName1);
pstmt1.executeUpdate();
// String s=null;模拟异常情况(断电、数据库崩溃)
// s.charAt(2);
sql = "update user set u_balance = u_balance ? where u_name = ? ";
pstmt2 = con.prepareStatement(sql);
pstmt2.setInt(1, money);
pstmt2.setString(2, UserName2);
pstmt2.executeUpdate();
con.commit();//事务完成
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCUtils.closeResource(rs, pstmt1, con);
JDBCUtils.closeResource(null, pstmt2, null);
}
}
}
分页查询的公式：
JDBC工具类：
package JDBCUtils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class JDBCUtils {
private static final String url="jdbc:mysql://localhost:3306/dy?useUnicode=true&characterEncoding=utf-8&useSSL=false";
private static final String user="root";
private static final String password="root";
private static ArrayList <Connection> Clist = new ArrayList<Connection>();//保存连接
static{
for(int i=0;i<10;i ){
Connection con = createConnection();//创建连接
Clist.add(con);//添加到容器中
}
}
public static Connection getConnection(){
if(Clist.isEmpty()==false){
Connection con = Clist.get(0);//得到容器中的连接
Clist.remove(con);
return con;
}else{
return createConnection();//创建连接
}
}
public static Connection createConnection(){
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(url,user,password);//建立和mysql数据库的连接
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}//选择注册驱动
return null;
}
public static void closeResource(ResultSet rs,Statement stmt,Connection con){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// try {
// if(con!=null)
// con.close();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
Clist.add(con);
}
public static void closeResource(ResultSet rs,PreparedStatement pstmt,Connection con){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// try {
// if(con!=null)
// con.close();
// } catch (SQLException e) {
// // TODO Auto-generated catch block
// e.printStackTrace();
// }
Clist.add(con);//这里的连接不需要关闭，因为在不断建立连接和取消连接的过程会消耗很多时间，所以引入了缓冲池（数据源）的概念
}
}
我们上面的工具类的数据源是我们自己利用容器去模拟它去实现的，实际上已经有一些现成的我们可以直接使用的，例如：dbcp、c3p0。
DBCP数据源（工具类版本2）：
package JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp2.BasicDataSource;
public class JDCPDataSource {
private static final String url="jdbc:mysql://localhost:3306/dy?useUnicode=true&characterEncoding=utf-8&useSSL=false";
private static final String user="root";
private static final String password="root";
private static BasicDataSource ds;
static{
ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl(url);
ds.setUsername(user);
ds.setPassword(password);
ds.setInitialSize(5);//设置初始连接
ds.setMaxTotal(20);//设置最大连接数
ds.setMinIdle(3);//设置最小空闲连接（一旦小于最小空闲连接它会自动创建连接以达到最小空闲连接）
}
public static Connection getConnection(){
try {
return ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void closeResource(ResultSet rs,Statement stmt,Connection con){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static void closeResource(ResultSet rs,PreparedStatement pstmt,Connection con){
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(pstmt!=null)
pstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
注意：使用dbcp时需要下载：第一个是核心包、后面两个都是核心包所依赖的东西，不下程序会显示找不到Class
c3p0使用方法类似，不做过多演示