一、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();
}
}
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
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
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;
}