JDBC编程和MySQL数据库

太过爱你忘了你带给我的痛 2021-09-19 01:40 1063阅读 0赞

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70][]

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);
    		}
    	}
    }

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70]: /images/20210725/7149359c33c44a73b867c85857a386f9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMzIzMjU2_size_16_color_FFFFFF_t_70 1]: /images/20210725/41a66617262641dfb4cb4eff37d44406.png