Mysql 和JDBC

用Java操作数据库时

• 加载驱动Class.forName("com.mysql.cj.jdbc.Driver");
• 连接数据库 DriverManager.getConnection(url, username, password);
• 获得执行sql的对象connection.createStatement();
• 获得返回的结果集resultSet = statement.execute(sql);
• 关闭数据库连接，释放资源

import java.sql.*;
public class jdbctest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url="jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
        String username="root";
        String password="123456";
        Connection connection = DriverManager.getConnection(url, username, password);
        Statement statement = connection.createStatement();
        String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
        ResultSet resultSet = statement.executeQuery(sql);
        System.out.println("subjectno"+"   subjectname");
        while (resultSet.next()){
            //System.out.println("subjectno"+"   subjectname");
            System.out.println("    "+resultSet.getObject("subjectno")+"        "+resultSet.getObject("subjectname"));
            //System.out.println("=====================");
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

注：这是mysql 8.0以上版本的操作，com.mysql.cj.jdbc.Driver和url的serverTimezone=Asia/Shanghai，与早期版本的有些区别

对象的解释

 Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动这是8.0版本后的驱动
 String url="jdbc:mysql://localhost:3306/school?"+
 "useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";
 String username="root";
 String password="123456";

url格式jdbc:mysql://localhost:3306/数据库名?/参数1&参数2/serverTimezone=Asia/Shanghai 8.0版本的时区要特别注意一下

Connection connection = DriverManager.getConnection(url, username, password);

Connection就代表数据库，常见的操作有

connection.commit();//事务提交
connection.rollback(); //事务回滚
connection.setAutoCommit();//事务自动提交

Statement PrepareStatement是具体的执行sql的对象

String sql = "SELECT subjectno,subjectname FROM subject WHERE subjectno <10"; statement.execute(sql);//执行任何sql语句
int num = statement.executeUpdate(sql);//执行更新、插入、删除的sql，返回受影响的行数（int） statement.executeQuery(sql);//执行查询的sql语句

ResultSet 返回查询的结果，封装了所有结果集

ResultSet resultSet = statement.executeQuery(sql);

while (resultSet.next()){  
        //columnLabel=数据库表中的列名  
        resultSet.getObject(columnLabel);  
        resultSet.getString(columnLabel);  
        resultSet.getInt(columnLabel);  
        resultSet.getFloat(columnLabel);  
        resultSet.getDate(columnLabel);  
        }

释放资源

resultSet.close();
statement.close();
connection.close();


将连接数据库的操作进行封装

先了解一下如何获取配置文件.properties
先创建配置文件db.properties

url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai
username=root
driver=com.mysql.cj.jdbc.Driver
password=123456

然后创建Utils

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String url=null;
    private static String username=null;
    private static String password=null;
    private static String driver=null;
    static {
        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//类加载器
            Properties properties = new Properties();
            properties.load(in);
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            driver = properties.getProperty("driver");
            Class.forName(driver);//驱动只要启动一次
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);//获取连接（数据库）
    }
    //释放资源
    public static void release(Connection connection , Statement statement , ResultSet resultSet){
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }if (statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }if (connection!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

然后进行增删改查

import utils.JdbcUtils;

import java.sql.*;

public class jdbctest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "SELECT `subjectno`,`subjectname` FROM `subject` WHERE `subjectno` <10";
            resultSet = statement.executeQuery(sql);//获取结果

            System.out.println("subjectno" + "   subjectname");
            while (resultSet.next()) {
                System.out.println("    " + resultSet.getObject("subjectno") + "        " + resultSet.getObject("subjectname"));
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

防止SQL注入

使用PreparedStatement

• 在sql语句中用 ？ 表示变量

import utils.JdbcUtils;

import java.sql.*;

public class jdbctest {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection connection = null;
        PreparedStatement statement = null;
        //ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            String sql = "INSERT INTO subject (`subjectno`,`subjectname`,`classhour`,`gradeid`) VALUES(?,?,?,?)";
            statement = connection.prepareStatement(sql);//预编译SQL，不执行
            statement.setInt(1,19);//第一个？
            statement.setString(2,"物理");//第二个？
            statement.setInt(3,700);
            statement.setInt(4,1);
            int row = statement.executeUpdate();

            if (row >0)
            {
                System.out.println("插入成功");
            }
        }
        catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            JdbcUtils.release(connection,statement,null);
        }

    }
}

给第几个？赋值就用preparedStatement.setObject(?的位置,值),从1开始