1 JDBC进行数据库操作
首先导入mysql-connector-java-5.1.39-bin.jar包,进行基础的增删改查,和转账的事务机制(错误中断回滚)
package com.yanzi.jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo01 {
public static void main(String[] args){
//selectAll();
//System.out.println(selectByUsernameOrPassword("yanzi","123' or '1'='1'"));
//System.out.println(selectByUp2("huang","222' or '1'='1'"));
//selectUserByPage(3,4);
//insert("东方不败","134565");
//delete(38);
//update(1,"2222");
transterAccounts("东方不败","令狐冲",1000);
}
public static void selectAll(){
Connection con=null;
Statement stmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
stmt = con.createStatement();
String sql="select * from user";
rs = stmt.executeQuery(sql);
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+","+rs.getString(2)); //索引从1开始
//System.out.println(rs.getInt("sid")+","+rs.getString("sname"));
}
} catch (Exception e) { //此处改为Exception 否则会报错
e.printStackTrace();
}finally {
JDBCUtils.close(rs,stmt,con);
}
}
/*
* 通过用户名和密码判定
*/
public static boolean selectByUsernameOrPassword(String username,String password) {
Connection con =null;
Statement stmt =null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8";
con = DriverManager.getConnection(url, "root", "root");
stmt = con.createStatement();
String sql = "select * from user where username ='"+username+"' and password='"+password+"'";
rs = stmt.executeQuery(sql);
if(rs.next()) {
return true;
}else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
/*
* 解决sql注入的问题
*/
public static boolean selectByUp2(String username,String password) {
Connection con =null;
PreparedStatement pstmt =null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8";
con = DriverManager.getConnection(url, "root", "root");
String sql = "select * from user where username = ? and password = ? ";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
rs = pstmt.executeQuery();
if(rs.next()) {
return true;
}else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(pstmt!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(con!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return false;
}
/*
* 分页查询
*/
public static void selectUserByPage(int pageNumber,int pageCount) {
Connection con =null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8", "root", "root");
pstmt= con.prepareStatement("select * from user limit ?,?");
pstmt.setInt(1, (pageNumber-1)*pageCount);
pstmt.setInt(2, pageCount);
rs=pstmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt(1)+","+rs.getString(2)+rs.getString(3));
//System.out.println(rs.getInt("id")+","+rs.getString("username")+","+rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(pstmt!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(con!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/*
* 插入
*/
public static void insert(String username,String password) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="insert into user(username,password) values(?,?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1,username);
pstmt.setString(2,password);
int result = pstmt.executeUpdate(); //返回值为影响的行数
} catch (Exception e) { //此处改为Exception 否则会报错
e.printStackTrace();
}finally {
JDBCUtils.close(rs,pstmt,con);
}
}
/*
* 删除操作
*/
public static void delete(int id) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="delete from user where id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setInt(1,id);
int result = pstmt.executeUpdate(); //返回值为影响的行数
if(result>0) {
System.out.println("删除成功");
}else {
System.out.println("删除失败");
}
} catch (Exception e) { //此处改为Exception 否则会报错
e.printStackTrace();
}finally {
JDBCUtils.close(rs,pstmt,con);
}
}
/*
* 更新操作
*/
public static void update(int id,String password) {
Connection con=null;
PreparedStatement pstmt=null;
ResultSet rs=null;
try {
con=JDBCUtils.getConnection();
String sql="UPDATE user SET password = ? WHERE id = ?";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, password);
pstmt.setInt(2,id);
int result = pstmt.executeUpdate(); //返回值为影响的行数
if(result>0) {
System.out.println("更新成功!");
}else {
System.out.println("更新失败!");
}
} catch (Exception e) { //此处改为Exception 否则会报错
e.printStackTrace();
}finally {
JDBCUtils.close(rs,pstmt,con);
}
}
/*
* 转账 事务机制
*/
public static void transterAccounts(String username1,String username2,int money) {
Connection con=null;
PreparedStatement pstmt1=null;
PreparedStatement pstmt2=null;
ResultSet rs=null;
try {
//con=JDBCUtils.getConnection();
con=DBCPDataSource.getConnection();
con.setAutoCommit(false);//开启事务
String sql="UPDATE user SET balance = balance - ? WHERE username = ?";
pstmt1 = con.prepareStatement(sql);
pstmt1.setInt(1, money);
pstmt1.setString(2,username1);
pstmt1.executeUpdate(); //返回值为影响的行数
//String s=null;
//s.charAt(2); //故意设置的错误来导致转账不成功
sql="UPDATE user SET balance = balance + ? WHERE username = ?";
pstmt2 = con.prepareStatement(sql);
pstmt2.setInt(1, money);
pstmt2.setString(2,username2);
pstmt2.executeUpdate(); //返回值为影响的行数
con.commit();//提交事务 同一个事物里的代码不成功,就会回滚
} catch (Exception e) { //此处改为Exception 否则会报错
e.printStackTrace();
}finally {
//JDBCUtils.close(pstmt2,pstmt1,con);
DBCPDataSource.close(pstmt2, pstmt1, con);
}
}
}
2.连接池
2.1 自己写的连接池
package com.yanzi.jdbc01;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
public class JDBCUtils {
private static final String connectionURL="jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8";
private static final String username="root";
private static final String password="root";
private static ArrayList<Connection> conList = new ArrayList<Connection>();
static {
for(int i=0;i<5;i++) {
Connection con = createConnection();
conList.add(con);
}
}
public static Connection getConnection() {
if(!conList.isEmpty()) {
Connection con = conList.get(0);
conList.remove(con);
return con;
}else {
return createConnection();
}
}
public static Connection createConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
return DriverManager.getConnection(connectionURL, username, password);
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Statement stmt,Connection con) {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(con);
}
public static void close(Statement stmt1,Statement stmt2,Connection con) {
closeStatement(stmt1);
closeStatement(stmt2);
closeConnection(con);
}
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeStatement(Statement stmt) {
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
/*try {
if(con!=null)
con.close();
} catch (SQLException e) {
e.printStackTrace();
}*/
conList.add(con);
}
}
2.2 DBCP连接池,要导入commons-dbcp2-2.5.0.jar commons-pool2-2.6.0.jar commons-logging-1.2.jar三个包,否则会报错
package com.yanzi.jdbc01;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp2.BasicDataSource;
public class DBCPDataSource {
private static final String connectionURL="jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8";
private static final String username="root";
private static final String password="root";
private static BasicDataSource ds;
static {
//初始化dbcp数据源
ds = new BasicDataSource();
ds.setDriverClassName("com.mysql.jdbc.Driver");
ds.setUrl(connectionURL);
ds.setUsername(username);
ds.setPassword(password);
ds.setInitialSize(5); //设置最小连接个数
ds.setMaxTotal(20); //设置最大连接个数
ds.setMinIdle(2); //设置最小空闲个数
}
public static Connection getConnection() {
try {
return ds.getConnection(); //通过dbcp得到的连接不需要归还,直接关闭即可
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Statement stmt,Connection con) {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(con);
}
public static void close(Statement stmt1,Statement stmt2,Connection con) {
closeStatement(stmt1);
closeStatement(stmt2);
closeConnection(con);
}
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeStatement(Statement stmt) {
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
try {
if(con!=null)
con.close(); //这里会把连接归还给连接池,并不会真正断开
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.3 C3P0连接池,需要导入c3p0-0.9.1.2.jar mchange-commons-java-0.2.15.jar两个包,否则会报错。
package com.yanzi.jdbc01;
import java.beans.PropertyVetoException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp2.BasicDataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0DataSource {
private static final String connectionURL="jdbc:mysql://localhost:3306/day22_jdbc?useUnicode=true&characterEncoding=UTF8";
private static final String username="root";
private static final String password="root";
private static ComboPooledDataSource ds;
static {
try {
ds.setDriverClass("com.mysql.jdbc.Driver");
ds = new ComboPooledDataSource();
ds.setJdbcUrl(connectionURL);
ds.setUser(username);
ds.setPassword(password);
ds.setMinPoolSize(5);
ds.setMaxPoolSize(20);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public static Connection getConnection() {
try {
return ds.getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
public static void close(ResultSet rs,Statement stmt,Connection con) {
closeResultSet(rs);
closeStatement(stmt);
closeConnection(con);
}
public static void close(Statement stmt1,Statement stmt2,Connection con) {
closeStatement(stmt1);
closeStatement(stmt2);
closeConnection(con);
}
private static void closeResultSet(ResultSet rs) {
try {
if(rs!=null)
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeStatement(Statement stmt) {
try {
if(stmt!=null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
private static void closeConnection(Connection con) {
try {
if(con!=null)
con.close(); //这里会把连接归还给连接池,并不会真正断开
} catch (SQLException e) {
e.printStackTrace();
}
}
}