【JDBC-Mysql】使用JDBC操作Mysql数据库-蒲公英云

【JDBC-Mysql】使用JDBC操作Mysql数据库

1）导入依赖
2）定义Connection连接类
3）使用JDBC进行Mysql数据库操作
- 3.1.写入
- 3.2.删除
- 3.3.修改
- 3.4.查询

1）导入依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.7.27</version>
</dependency>

2）定义Connection连接类

public class Conn {
    //定义Driver
    public static final String driver = "com.mysql.jdbc.Driver";
    //定义getConnection方法获取Connection
    public static Connection getConnection(String url, String username, String password) {
        Connection conn = null;
        try {
            //注册驱动
            Class.forName(driver);
            //获取Connection
            conn = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
    //关闭连接
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    //关闭连接
    public static void close(Connection conn, PreparedStatement ps) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

3）使用JDBC进行Mysql数据库操作

3.1.写入

public class Test {
    private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
    private static final String tmpUsername = "root";
    private static final String tmpPassword = "123456";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    public static void main(String[] args) {
        conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
        try {
            //编辑sql，返回PreparedStatement状态
            ps = conn.prepareStatement("insert into `table` values(1234, '张三')");
            //执行写入数据的操作
            rs = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            Conn.close(conn, ps, rs);
        }
    }
}

3.2.删除

public class Test {
    private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
    private static final String tmpUsername = "root";
    private static final String tmpPassword = "123456";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    public static void main(String[] args) {
        conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
        try {
            //编辑sql，返回PreparedStatement状态
            ps = conn.prepareStatement("delete from `table` where id = ?");
            //定义参数（?按照顺序进行参数配置，如下是删除id为1234的数据）
            ps.setInt(1,1234)
            //执行删除数据的操作
            rs = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            Conn.close(conn, ps, rs);
        }
    }
}

3.3.修改

public class Test {
    private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
    private static final String tmpUsername = "root";
    private static final String tmpPassword = "123456";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    public static void main(String[] args) {
        conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
        try {
            //编辑sql，返回PreparedStatement状态
            ps = conn.prepareStatement("update `table` set name = '张三' where id = ?");
            //定义参数（?按照顺序进行参数配置，如下是修改id为1234的数据）
            ps.setInt(1,1234)
            //执行修改数据的操作
            rs = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            Conn.close(conn, ps, rs);
        }
    }
}

3.4.查询

public class Test {
    private static final String tmpUrl = "jdbc:mysql://192.168.1.1:3306/test?characterEncoding=utf-8";
    private static final String tmpUsername = "root";
    private static final String tmpPassword = "123456";
    private static Connection conn;
    private static PreparedStatement ps;
    private static ResultSet rs;
    public static void main(String[] args) {
        conn = Conn.getConnection(tmpUrl, tmpUsername, tmpPassword);
        try {
            //编辑sql，返回PreparedStatement状态
            ps = conn.prepareStatement("SELECT `id`, `name` FROM `table`");
            //返回ResultSet结果集
            rs = ps.executeQuery();
            //遍历结果集
            while (rs.next()) {
                //获取每条数据的id，name（两种写法都可以）
                //int id = rs.getInt("id");
                int id = rs.getInt(1);
                //String name = rs.getString("name");
                String name = rs.getString(2);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            Conn.close(conn, ps, rs);
        }
    }
}