IDEA连接MySQL数据库

MySQL数据库

---

IDEA 方面

jar下载

下载
https://dev.mysql.com/downloads/connector/j/

导入jar包

解压之后再导入

加载完成，查看

在2中添加所需的表

代码测试

package com.mysqlTest.chen;
import java.sql.*;
public class JdbcFirstDemo {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");

        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC";
        String username = "cwq1";
        String password = "chen";

        //3.连接成功，数据库对象Connection代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //4.执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();

        //5.执行语句
        String sql = "SELECT * FROM grade";

        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("gradeid=" + resultSet.getObject("gradeid"));
            System.out.println("gradename=" + resultSet.getObject("gradename"));
            System.out.println("===========");
        }
        
        //6.关闭
        resultSet.close();
        statement.close();
        connection.close();
    }
}

改进：

• db.properties
  

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=UTC
username=cwq1
password=chen

• JdcUtils.java
  

package com.mysqlTest.chen1.utils;

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

public class JdbcUtils {
    private static String driver = null;
    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //驱动只加载一次
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection = DriverManager.getConnection(url, username, password);
        return connection;
    }

    // 释放连接
    public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {

        if (resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

• 测试使用

package com.mysqlTest.chen1;
import com.mysqlTest.chen1.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            // 获取数据库连接
            connection = JdbcUtils.getConnection();
            // 获得SQL的执行对象
            statement = connection.createStatement();
            String sql = "INSERT INTO grade(gradeid,gradename)VALUES(9,'研一')";
            int i = statement.executeUpdate(sql);
            if (i>0) {
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

---

注入问题的解决：Preparestatement

package com.mysqlTest.chen2;
import com.mysqlTest.chen1.utils.JdbcUtils;
import java.sql.*;

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        Statement statement = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            // 区别：预编译sql,不执行
            // 使用问号占位符代替参数
            String sqlInsert = "INSERT INTO grade(gradeid,gradename)VALUES(?,?)";
            preparedStatement = connection.prepareStatement(sqlInsert);
            // 手动给参数赋值
            preparedStatement.setObject(1,6);
            preparedStatement.setObject(2,"研三");
            // 执行
            boolean execute = preparedStatement.execute();
            if (execute==true) {
                System.out.println("插入成功");
            }
            // 查询
            String sqlSelect = "select * from grade where gradeid<7";
            resultSet = preparedStatement.executeQuery(sqlSelect);
            while (resultSet.next()) {
                System.out.println("gradeid = " + resultSet.getObject("gradeid"));
                System.out.println("gradename = " + resultSet.getObject("gradename"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

---

事务

package com.mysqlTest.chen1;

import com.mysqlTest.chen1.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestTransation {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();
            // 关闭数据库的自动提交，也就是开启事务
            connection.setAutoCommit(false);
            // 执行事务
            String sql1 = "UPDATE account SET money = money-100 WHERE userid = 1";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            String sql2 = "UPDATE account SET money = money+100 WHERE userid = 2";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            // 提交事务
            connection.commit();
            System.out.println("成功");
        } catch (SQLException throwables) {
            try {
                // 如果事务失败，则回滚
                connection.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}