Java使用mysql三个基础例子

Java使用mysql三个基础例子

示例1：java调用mysql的最基础的方式查询数据：

package com.cc.mysql_learn;

import java.sql.*;

public class MysqlLearn1 {
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");////加载驱动com.mysql.cj.jdbc.Driver
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/cclearn?useSSL=true&characterEncoding=utf8&useUnicode=true&serverTimezone=UTC","root","tester");//连接数据库
        PreparedStatement preparedStatement = connection.prepareStatement("select * from student");//定义查询语句
        ResultSet rs = preparedStatement.executeQuery();//执行mysql语句
        while(rs.next()){//判断，如果有数据，则re.next为true
            System.out.println(rs.getInt("id"));//通过rs的getInt等方法获取mysql查到的字段（id代表数据表中为id的列名）
        }
        
        //释放连接
        rs.close();
        preparedStatement.close();
        connection.close();
    }
}

 

示例2：使用mysql判断账号密码是否正确

先附上创建数据表和预置数据的命令，但前提你得有一个数据库，并使用use命令进入数据库：

-- 创建数据表
CREATE TABLE `users` (
  `uid` bigint(20) NOT NULL COMMENT '用户ID',
  `user_name` varchar(32) NOT NULL  COMMENT '用户账号',
  `password` varchar(64) NOT NULL  COMMENT '用户混淆密码',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `u_user_name` (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表'
-- 预置一条信息
insert into users(uid,user_name,password) values(1,"cc","123456");

 

以下是java调用数据库验证密码的代码：

package com.cc.mysql_learn;

import java.sql.*;

public class MysqlPractice1 {
    
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        MysqlPractice1 mysqlPractice1 = new MysqlPractice1();
        mysqlPractice1.verifyPassword("test","123");//验证一个错误的账号密码
        mysqlPractice1.verifyPassword("cc","123456");//验证一个正确地账号密码
    }
    
    public boolean verifyPassword(String username,String password) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");//加载驱动com.mysql.cj.jdbc.Driver
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/cclearn?useSSL=true&characterEncoding=utf8&useUnicode=true&serverTimezone=UTC","root","tester");//连接数据库
        PreparedStatement preparedStatement = connection.prepareStatement("select * from users where user_name=? and password=?");//定义查询语句，prepareStatement支持预编译，将变量通过?表示，提高效率且更安全，有效防止musql注入攻击。
        preparedStatement.setObject(1,username);//设置user_name
        preparedStatement.setObject(2,password);//设置password
        ResultSet rs = preparedStatement.executeQuery();//执行mysql语句
        if(rs.next()){//如果没有查到数据，则账号密码验证不通过，查到数据，则验证通过
            System.out.println("账号密码正确，验证通过");
            return true;
        }
        else {
            System.out.println("账号或密码错误，验证失败");
            return false;
        }
        //释放连接
        rs.close();
        preparedStatement.close();
        connection.close();
    }
}

示例3：封装工具类对jdbc连接进行解耦

新增配置文件：mysql.properties

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

新增工具类JdbcUtils：

package com.cc.mysql_learn;
​
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
​
public class JdbcUtils {
    private static String driver;
    private static String url;
    private static String username;
    private static String password;
    static{//写在静态代码块中，类被加载的时候就运行了，只运行一次，优先于各种代码块以及构造函数
        try{
            Properties properties = new Properties();
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("mysql.properties");
            properties.load(in);//加载mysql.properties文件
​
            driver = properties.getProperty("driver");//获取mysql.properties的信息
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
​
            Class.forName(driver);//加载驱动
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
​
    public static Connection getConnection() throws ClassNotFoundException, SQLException {
        return DriverManager.getConnection(url,username,password);//返回连接对象
    }
​
    public static void closeConnection(Connection connection, PreparedStatement preparedStatement, ResultSet rs) throws SQLException {
        //依次释放
        if(rs != null){
            rs.close();
        }
        if(preparedStatement != null){
            preparedStatement.close();
        }
        if(connection != null){
            connection.close();
        }
    }
}

简化后的主要业务代码：

public static void main(String[] args) throws SQLException, ClassNotFoundException {
        Connection connection = JdbcUtils.getConnection();//调用工具类获取连接
        PreparedStatement preparedStatement = connection.prepareStatement("select * from users;");
        ResultSet rs = preparedStatement.executeQuery();
        if(rs.next()){
            System.out.println(rs.getObject("user_name"));
        }
        JdbcUtils.closeConnection(connection,preparedStatement,rs);//调用工具类释放连接
    }