JDBC编程和MySQL数据库

 JDBCUtils .java:

package com.sikiedu.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;

import com.mysql.jdbc.PreparedStatement;



public class JDBCUtils {
	private static final String connectionURL="jdbc:mysql://localhost:3306/web01?useSSL=false&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 conn=createConnection();
			conList.add(conn);
		}
	}
	
	public static Connection getConnection(){
		if(conList.isEmpty()==false){
			Connection conn=conList.get(0);
			conList.remove(conn);
			return conn;
		}else{
			return createConnection();	
		}
	}
	
	public static Connection createConnection(){
		try {
			Class.forName("com.mysql.jdbc.Driver");
			return  DriverManager.getConnection(connectionURL, username, password);
		} catch (Exception e) {
			
			e.printStackTrace();
		}
		return null;
	}
	
	public static void close(ResultSet rs,PreparedStatement pstmt,Connection conn){
		closeResultSet(rs);
		closePreparedStatement(pstmt);
		closeConnection(conn);
		
	}
	public static void close(PreparedStatement pstmt1,PreparedStatement pstmt2,Connection conn){
		closePreparedStatement(pstmt1);
		closePreparedStatement(pstmt2);
		closeConnection(conn);
	}
	public static void closeResultSet(ResultSet rs){
		if(rs!=null)
			try {
				rs.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
	}
	public static void closePreparedStatement(PreparedStatement pstmt){
		if(pstmt!=null)
			try {
				pstmt.close();
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
	}
	public static void closeConnection(Connection conn){
//		if(conn!=null)
//			try {
//				conn.close();
//			} catch (SQLException e) {
//				
//				e.printStackTrace();
//			}
		conList.add(conn);
  }
		
}

 JDBCDemo01 .java:

package com.sikiedu.jdbc01;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

public class JDBCDemo01 {
	
	public static void main(String[] args) {
		selectAll();
		
		System.out.println(selectByUsernameAndPassword("张三", "333"));
		
//		insert("赵六","666");
//		
//		delete(6);
		
//		update(9,"999");
		
		transferAccounts("张三", "李四", 1000);
	}
	
	//查询所有记录
	public static void selectAll(){	
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			conn=(Connection) JDBCUtils.getConnection();

			String sql="select * from user";
			pstmt= (PreparedStatement) conn.prepareStatement(sql);
			
			rs= pstmt.executeQuery(sql);
			
			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 {
			JDBCUtils.close(rs, pstmt, conn);
		}
	}

	//查询用户名和密码是否存在
	public static boolean selectByUsernameAndPassword(String username,String password){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;

		String url="jdbc:mysql://localhost:3306/web01?useSSL=false&useUnicode=true&characterEncoding=UTF8";

		try {
			
			conn=(Connection) DriverManager.getConnection(url, "root", "root");
			
			String sql="Select * from user where username=? and password=?";
			pstmt= (PreparedStatement) conn.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 {
			JDBCUtils.close(rs, pstmt, conn);
		}
		
		return false;
	}

	//插入用户名和密码
	public static void insert(String username,String password){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			conn=(Connection) JDBCUtils.getConnection();

			String sql="insert into user(username,password) values(?,?)";
			pstmt= (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setString(1, username);
			pstmt.setString(2, password);
			int result= pstmt.executeUpdate();
			
		} catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
	}

	//删除记录(通过id)
	public static void delete(int id){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			conn=(Connection) JDBCUtils.getConnection();

			String sql="delete from user where id=?";
			pstmt= (PreparedStatement) conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			int result= pstmt.executeUpdate();
			if(result>0)
			{
				System.out.println("删除成功");
			}else{
				System.out.println("删除失败");
			}
		} catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
	}

	//修改记录
	public static void update(int id,String newPassword){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		try {
			conn=(Connection) JDBCUtils.getConnection();

			String sql="update user set password=? where id=?";
			pstmt= (PreparedStatement) conn.prepareStatement(sql);
			
			pstmt.setString(1, newPassword);
			pstmt.setInt(2, id);
			
			int result= pstmt.executeUpdate();
			if(result>0)
			{
				System.out.println("修改成功");
			}else{
				System.out.println("修改失败");
			}
		} catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			JDBCUtils.close(rs, pstmt, conn);
		}
	}

	//转账例子
	public static void transferAccounts(String username1,String username2,int money){
		Connection conn=null;
		PreparedStatement pstmt1=null;
		PreparedStatement pstmt2=null;
		ResultSet rs=null;
		
		try {
			conn=(Connection) JDBCUtils.getConnection();

			
			conn.setAutoCommit(false);//开启事务
			
			String sql="update user set balance=balance-? where username=?";
			pstmt1= (PreparedStatement) conn.prepareStatement(sql);
			pstmt1.setInt(1, money);
			pstmt1.setString(2, username1);
			pstmt1.executeUpdate();
			
			sql="update user set balance=balance+? where username=?";
			pstmt2= (PreparedStatement) conn.prepareStatement(sql);
			pstmt2.setInt(1, money);
			pstmt2.setString(2, username2);
			pstmt2.executeUpdate();
			
			conn.commit();//提交事务
			
		} catch (Exception e) {
			
			e.printStackTrace();
		} finally {
			JDBCUtils.close(pstmt2, pstmt1,conn);
		}
	}
}