Java 数据库

Ubuntu安装mysql

sudo apt-get update
sudo apt-get install mysql-server mysql-client

sudo mysql_secure_installation

mysql_secure_installation脚本设置的东西：更改root密码、移除MySQL的匿名用户、禁止root远程登录、删除test数据库。使用上面的这些选项可以提高MySQL的安全。

使用root用户登陆

mysql -u root -p

创建MySQL数据库

create database test;

上面命令创建了一个名为test的数据库。

创建用户

grant all on test.* to "man_user" identified by "test1234";

新用户登录

mysql -u ltb -p test

创建表

CREATE TABLE user (id INT, name VARCHAR(20), email VARCHAR(20));

插入记录

insert into user(id, name, email) values(1, "bar", "bar@gmail.com");
insert into user(id, name, email) values(2, "foo", "foo@163.com");
insert into user(id, name, email) values(3, "cat", "cat@gmail.com");

简单查询

SELECT * FROM user;

退出mysql命令行

quit

停止mysql数据库服务

sudo systemctl stop mysql.service

启动mysql数据库服务

sudo systemctl start mysql.service

java连接mysql

public class MySQLDemo{
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=fasle";
    
    static final String USER = "ltb";
    static final String PASS = "mima";
    
    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, USER, PASS);
            
            // 执行查询
            System.out.println("实例化Statement对象...");
            stmt = conn.createStatement();
            String sql = "SELECT id, name, email FROM user";
            ResultSet rs = stmt.executeQuery(sql);
            
            // 展开结果集数据库
            while(rs.next()){
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                
                System.out.println("id: " + id);
                System.out.println("name: " + name);
                System.out.println("email: " + email);             
            }
            
            // 完成后关闭
            rs.close();
            stmt.close();
            conn.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            try{
                if(stmt!=null){
                    stmt.close();
                }
            }catch(Exception e){}
			
            try{
                if(conn!=null){
                    conn.close();
                }
            }catch(Exception e){
                e.printStackTrace();
            }
        }
        System.out.println("Goodbye!");
    }
}

增添数据

public class MySQLInsert {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false";

    static final String USER = "ltb";
    static final String PASS = "mima";

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement psql = null;
        ResultSet rs = null;

        try {
            Class.forName(JDBC_DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        System.out.println("连接数据库...");
        try {
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String sql = "INSERT INTO user(id, name, email)" + "VALUE(?, ?, ?)";
        try {
            psql = conn.prepareStatement(sql);
            psql.setInt(1, 4);
            psql.setString(2, "tiger");
            psql.setString(3, "tiger@qq.com");
            psql.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

删除数据

package lesson20;

import java.sql.*;

public class MySQLDelete {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false";

    static final String USER = "ltb";
    static final String PASS = "mima";

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement psql = null;
        ResultSet rs = null;

        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("连接数据库...");

            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            psql = conn.prepareStatement("DELETE FROM user WHERE id = ?");
            psql.setInt(1, 2);
            psql.executeUpdate();
            psql.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

更改数据

public class MySQLUpdate {
    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false";

    static final String USER = "ltb";
    static final String PASS = "mima";

    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement psql = null;
        ResultSet rs = null;

        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);

            psql = conn.prepareStatement("UPDATE user SET id = ? WHERE id = ?");
            psql.setInt(1, 5);
            psql.setInt(2, 1);
            psql.executeUpdate();
            psql.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
}

参考

https://blog.csdn.net/chenqianfang/article/details/79958518

https://www.runoob.com/java/java-mysql-connect.html

https://www.cnblogs.com/centor/p/6142775.html