JDBC连接MySQL增删改查

年末，公司比较闲，再重新整理一遍，以下都是自己动手实践过且运行成功

不管框架使用起来是如何的方便，提高多少的开发效率，但是都是从基础之上开始的，就像房子的地基

正文：

使用JDBC连接MySQL完成增删改查，使用User类作为对应表的实体类来操作

当然，首先需要下载jdbc的jar并添加到对应工程

比较规范的连接和使用数据库

package TestJDBC;

import java.sql.*;

public class SimpleInsert {
	
	// JDBC 驱动名及数据库 URL
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
    static final String DB_URL = "jdbc:mysql://localhost:3306/test";
 
    // 数据库的用户名与密码，需要根据自己的设置
    static final String USERNAME = "admin";
    static final String PASSWORD = "123456";

	public static void main(String[] args) {
		Connection conn = null;
        Statement stmt = null;
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
        
            // 打开链接
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);
        
            // 执行查询
            System.out.println(" 实例化Statement对象...");
            stmt = conn.createStatement();
            //两种插入都可以实现，数据库里正常显示
            /*String sql1 = "INSERT INTO user VALUES(7,'夏历六号','xlll')";
            stmt.execute(sql1);*/
            
            /*PreparedStatement stmt2 = conn.prepareStatement("INSERT INTO user VALUES(3,'夏历二号','xl')");
            stmt2.executeUpdate();*/
           
            String sql;
            sql = "SELECT id, name, password FROM user";
            ResultSet rs = stmt.executeQuery(sql);
        
            // 展开结果集数据库
            while(rs.next()){
                // 通过字段检索
                int id  = rs.getInt("id");
                String name = rs.getString("name");
                String url = rs.getString("password");
    
                // 输出数据
                System.out.print("ID: " + id);
                System.out.print(", 名字: " + name);
                System.out.print(", 密码: " + url);
                System.out.print("\n");
            }
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }// 什么都不做
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
		

	}
}

User类

package TestJDBC;

public class User {
	private int id;
	private String name;
	private String password;
	private String phone;
	
	public User(){	
	}
	public User(int id, String name, String password){
		this.id = id;
		this.name = name;
		this.password = password;
	}
	public User(int id, String name, String password, String phone){
		this.id = id;
		this.name = name;
		this.password = password;
		this.phone = phone;
	}
	
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
}

增删改查

package TestJDBC;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class JdbcForUser {
	
	static final String DRIVER = "com.mysql.jdbc.Driver";
	static final String URL = "jdbc:mysql://localhost:3306/test";
	static final String USER_NAME = "admin";
	static final String PASSWORD = "123456";
    
	public static void main(String[] args) throws Exception{
	    JdbcForUser jfu = new JdbcForUser();
	    
		jfu.addUser(new User(3,"oo","oo","333"));
		jfu.addUser(new User(4,"uu","uu","444"));
		
	    User user = jfu.getById(2);
	    System.out.println(user.getName()+" "+user.getPhone());
	    
	    jfu.updateUser(new User(4,"ou","ou","444"));
	    
	    List<User> users = new ArrayList<User>();
	    users = jfu.getAll();
	    for(User u : users){
	    	System.out.println(u.getName());
	    }
	    
	    List<Integer> ids = new ArrayList<Integer>();
	    ids.add(3);
	    ids.add(4);
	    jfu.betchDelUser(ids);
	
	}
	
    //获取连接方法
	public static Connection getConnection() throws Exception{
		Class.forName(DRIVER);
		Connection cnt = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
		return cnt;	
	}
	//增
	public void addUser(User user) throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " INSERT INTO user (id,name,password,phone) VALUES (?,?,?,?) ";
		PreparedStatement stmt = conn.prepareStatement(sql);//预编译sql
		stmt.setInt(1, user.getId());
		stmt.setString(2, user.getName());
		stmt.setString(3, user.getPassword());
		stmt.setString(4, user.getPhone());
		stmt.execute();	
	}
	//删（根据id 一条记录）
	public void delUser(int id) throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " DELETE FROM user WHERE id = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		stmt.execute();
	}
	//批量删除
	public void betchDelUser(List<Integer> ids) throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " DELETE FROM user WHERE id = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		for(Integer i : ids){
			stmt.setInt(1, i);
			stmt.execute();
		}
	}
	//改
	public void updateUser(User user) throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " UPDATE user SET name = ?, password = ?, phone =? WHERE id = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setString(1, user.getName());
		stmt.setString(2, user.getPassword());
		stmt.setString(3, user.getPhone());
		stmt.setInt(4, user.getId());
		stmt.execute();
	}
	//查，单条
	public User getById(int id) throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " SELECT * FROM user WHERE id = ?";
		PreparedStatement stmt = conn.prepareStatement(sql);
		stmt.setInt(1, id);
		ResultSet rs = stmt.executeQuery();
		User user = new User();
		while(rs.next()){
			//user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			//user.setPassword(rs.getString("password"));
			user.setPhone(rs.getString("phone"));
		}
		return user;
	}
	//查询全部
	public List<User> getAll() throws Exception{
		Connection conn = JdbcForUser.getConnection();
		String sql = " SELECT * FROM user";
		PreparedStatement stmt = conn.prepareStatement(sql);
		ResultSet rs = stmt.executeQuery();
		List<User> users = new ArrayList<User>();
		User user = null;
		while(rs.next()){
			user = new User();//需要在这里new，在上面new出来在这里使用，查询出来的会是同一个user，即同一条记录
			user.setId(rs.getInt("id"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setPhone(rs.getString("phone"));
			users.add(user);
		}
		return users;
		
	}
}

——————over——————