Java之Mysql数据库的连接及DML操作(增删改)

JAVA MySQL数据库连接与DML操作（全称是Data Manipulate Language，数据操作语言，即是增删改，相当于mysql中的insert、delete和update）

1、加载驱动（mysql-connector-java-5.1.5-bin.jar）
2、创建连接

import java.sql.*;

public class DBUtil {
    public String username = "root";
    public String password = "123456";
    private String url="jdbc:mysql://localhost:3306/+"+"你的要连接的数据库名"+"+?useSSL=true&characterEncoding=utf-8";
    /**
     * 1. 实现数据库连接的方法
     */
    public Connection getConn() {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");

            conn = DriverManager.getConnection(url, username, password);
            System.out.println("连接数据库成功.");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 2. 释放数据库连接
     */
    public void closeConn(ResultSet rs, PreparedStatement pstm, Connection conn) throws Exception {
        if (rs != null) {
            rs.close();
        }
        if (pstm != null) {
            pstm.close();
        }
        if (conn != null) {
            conn.close();
        }
    }
}

数据库的增删改查操作

1、获取刚刚创建的Connection对象
2、写sql语句
3、得到statement对象
4、执行sql语句，得到结果集
5、处理结果集
6、关闭资源
7、增加数据

插入数据

    DBUtil db = new DBUtil();
    private Connection conn;

    /*
    插入数据
     */
    @Override
    public void insertUser(TeamWorker newUser) throws Exception {
        Connection conn = db.getConn();
        PreparedStatement pstm = null;
        String sql_insert = "insert into teamworkerinfo(tb_pid,tb_pname,tb_ppsw) values(?,?,?)";	//sql语言
        pstm = conn.prepareStatement(sql_insert);
		
	//填充sql语句中的？
        pstm.setString(1, newUser.getId());
        pstm.setString(2, newUser.getName());
        pstm.setString(3, newUser.getPwd());

	//使用executeUpdate函数执行sql语句
        int row = pstm.executeUpdate();
        System.out.println("新增用户成功" + row + "行受到影响");
        //释放对数据库的连接
        db.closeConn(null, pstm, conn);
    }

修改数据

    DBUtil db = new DBUtil();
    private Connection conn;
    /*
    从数据库中修改用户信息
    */
    @Override
    public void updateUser(String id, TeamWorker modUser) throws Exception {
        Connection conn = db.getConn();
        PreparedStatement pstm = null;
        String sql_update = "update teamworkerinfo set tb_pname=?tb_ppsw=? where tb_pid=?";
        pstm = conn.prepareStatement(sql_update);

        pstm.setString(1,modUser.getName());
        pstm.setString(2,modUser.getPwd());
        pstm.setString(3,id);

        int row = pstm.executeUpdate();
        System.out.println("修改用户成功"+row+"行受到影响");
        db.closeConn(null, pstm, conn);
    }

删除数据

 	DBUtil db = new DBUtil();
    private Connection conn;
	/*
    从数据表中删除用户信息
     */
    @Override
    public void deleteUser(String id) throws Exception {
        Connection conn = db.getConn();
        PreparedStatement pstm = null;
        String sql_delete = "delete from teamworkerinfo where tb_pid=?";
        pstm = conn.prepareStatement(sql_delete);

        pstm.setString(1,id);

        int row = pstm.executeUpdate();
        System.out.println("删除用户成功"+row+"行受到影响");
        db.closeConn(null, pstm, conn);
    }