Java之JDBC

提取工具类

package com.Fang.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 JDBCUtil {
	
	//设置常用的属性
	private static final String connectionURL = "jdbc:mysql://localhost:3306/web01?useSSL=false";
	private static final String  username = "root";
	private static final String password = "root";
	
	public static Connection getConnection() {//建立连接
		
		try {
			Class.forName("com.mysql.jdbc.Driver");//获取架包格式
			String url = connectionURL;
			return DriverManager.getConnection(url, username, password);//返回获取连接
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
	}
	
	public static void close(ResultSet rs,PreparedStatement stmt,Connection con) {//关闭操作
		closeResultSet(rs);
		closePreparedStatement(stmt);
		closeConnection(con);
	}
	
	public static void close2(PreparedStatement stmt1,PreparedStatement stmt2,Connection con) {//事务关闭操作2
		closePreparedStatement(stmt1);
		closePreparedStatement(stmt2);
		closeConnection(con);
	}
	
	@SuppressWarnings("unused")
	private static void closeResultSet(ResultSet rs) {
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}//关闭连接
	}
	
	@SuppressWarnings("unused")
	private static void closePreparedStatement(PreparedStatement stmt) {
		try {
			if(stmt != null)
				stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@SuppressWarnings("unused")
	private static void closeConnection(Connection con) {
		try {
			if(con!=null)
				con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}

demo

package com.Fang.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(selectByUsernamePassword("123","456"));
//		selectByPage(5,4);
		
//		insert("hhhh","789456");
//		delete(56);
//		update(1,"qweasdasasd");
		transferCount("wanwu","lisi",50);
	}
	public static void selectAll() {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//使用什么驱动连接数据库
//			String url="jdbc:mysql://localhost:3306/web01?useSSL=false";//获取连接路径
//			String user="root";//设置用户名
//			String password="root";//设置密码
//			con = DriverManager.getConnection(url, user, password);//建立连接
			
			con=JDBCUtil.getConnection();
			
			stmt=con.prepareStatement("select * from user");//获取结果集
			rs = stmt.executeQuery();//对结果集进行操作
			while(rs.next()) {//循环读取结果集
				System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"---"+rs.getString(3));
			}
			
			
		} catch (ClassNotFoundException e) {
			
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			JDBCUtil.close(rs, stmt, con);
			
		}
		
	}

	public static boolean selectByUsernamePassword(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/web01?useSSL=false";
			con=DriverManager.getConnection(url, "root", "root");
			
			stmt=con.createStatement();
			String sql="select * from user where username='"+username+"' and password='"+password+"'";
			System.out.println(sql);
			rs=stmt.executeQuery(sql);
			
			if(rs.next()) {
				return true;
				
			}else {
				return false;
				
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}
	
	public static void selectByPage(int pageNumber,int pageCount) {//分页
		Connection con=null;
		Statement stmt=null;
		ResultSet rs=null;
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/web01?useSSL=false";
			con=DriverManager.getConnection(url, "root", "root");
			
			stmt=con.createStatement();
			String sql="select * from user limit "+pageNumber+","+pageCount;//limit用于分页，前面用于从何处开始，后面每页显示多少
			rs=stmt.executeQuery(sql);
			while(rs.next()) {
				System.out.println(rs.getInt(1)+"---"+rs.getString(2)+"---"+rs.getString(3));
			}
			
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public static void insert(String username,String password) {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		
		try {
			con=JDBCUtil.getConnection();
			String sql = "insert into user(username,password) values(?,?)";
			stmt = con.prepareStatement(sql);
			stmt.setString(1, username);
			stmt.setString(2, password);
			
			int result=stmt.executeUpdate();//update用于增删改，返回受影响的行数
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs, stmt, con);
		}
			
		
	}
	
	public static void delete(int id) {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		
		try {
			con=JDBCUtil.getConnection();
			String sql = "delete from user where id=?";
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, id);
			
			int result=stmt.executeUpdate();//update用于增删改，返回受影响的行数
			if(result>0) {
				System.out.println("success!");
			}else {
				System.out.println("failed");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs, stmt, con);
		}
		
	}
	
	public static void update(int id,String newpassword) {
		Connection con=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		
		try {
			con=JDBCUtil.getConnection();
			String sql = "update user set password = ? where id = ?";
			stmt = con.prepareStatement(sql);
			stmt.setString(1, newpassword);
			stmt.setInt(2, id);
			
			int result=stmt.executeUpdate();//update用于增删改，返回受影响的行数
			if(result>0) {
				System.out.println("success!");
			}else {
				System.out.println("failed");
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close(rs, stmt, con);
		}
	}
	
	//事务，指的是需要同时操作，eg转钱的操作，如果需要同时加减
	
	public static void transferCount(String username1,String username2,int momeny) {
		
		Connection con=null;
		PreparedStatement stmt1=null;
		PreparedStatement stmt2=null;
		
		try {
			con=JDBCUtil.getConnection();
			String sql = "update user set balance = balance - ? where username = ?";
			stmt1 = con.prepareStatement(sql);
			stmt1.setInt(1, momeny);
			stmt1.setString(2, username1);
			stmt1.executeUpdate();
			
			sql = "update user set balance = balance + ? where username = ?";
			stmt2 = con.prepareStatement(sql);
			stmt2.setInt(1, momeny);
			stmt2.setString(2, username2);
			
			stmt2.executeUpdate();//update用于增删改，返回受影响的行数
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.close2(stmt1, stmt2, con);
		}
		
	}
}