目录
一、sql 语句二、获取数据库连接三、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();
}
}
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;
}
