Statement 和 PreparedStatement 的使用

一、sql 语句

CREATE DATABASE mytest DEFAULT CHARACTER SET UTF8;

USE mytest;

CREATE TABLE users (
	user_id INT(32) PRIMARY KEY,
	user_name VARCHAR(50) UNIQUE,
	password VARCHAR(50)
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

INSERT INTO users(user_id, user_name, password) VALUES(1002, 'root', 'admin');

二、获取数据库连接

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    private static String ip = "127.0.0.1";
    private static String port = "3306";
    private static String database = "mytest";
    private static String encoding = "utf-8";
    private static String username = "root";
    private static String password = "admin";

    static {
        try {
            // 注册数据库驱动
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取数据库连接
     *
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        String url = String.format("jdbc:mysql://%s:%s/%s?characterEncoding=%s",
                ip, port, database, encoding);
        return DriverManager.getConnection(url, username, password);
    }
}

三、Statement

/**
 * 检查账号密码是否正确
 *
 * @param username 用户名
 * @param password 用户密码
 * @return
 */
public boolean check(String username, String password) {
    Connection connection = null;
    Statement statement = null;
    ResultSet rs = null;
    String usernameFromDB = null;
    String passwordFromDB = null;
    try {
        String sql = "SELECT u.user_name, u.password FROM users u WHERE u.user_name = '%s'";
        sql = String.format(sql, username);

        connection = DBUtil.getConnection();
        statement = connection.createStatement();
        rs = statement.executeQuery(sql);
        if (rs.next()) {
            usernameFromDB = rs.getString("user_name");
            passwordFromDB = rs.getString("password");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    if (username.equals(usernameFromDB) && password.equals(passwordFromDB)) {
        return true;
    }
    return false;
}

四、PreparedStatement

/**
 * 检查账号密码是否正确
 *
 * @param username 用户名
 * @param password 用户密码
 * @return
 */
public boolean check(String username, String password) {
    Connection connection = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    String usernameFromDB = null;
    String passwordFromDB = null;
    try {
        String sql = "SELECT u.user_name, u.password FROM users u WHERE u.user_name = ?";
        connection = DBUtil.getConnection();
        ps = connection.prepareStatement(sql);
        ps.setString(1, username);
        rs = ps.executeQuery();
        if (rs.next()) {
            usernameFromDB = rs.getString("user_name");
            passwordFromDB = rs.getString("password");
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    if (username.equals(usernameFromDB) && password.equals(passwordFromDB)) {
        return true;
    }
    return false;
 }