1.数据库连接四要素不应该写死在代码中,扩展性不高,应该抽取到配置文件中动态读取:
扩展文件名.properties
DRIVER_CLASS_NAME = com.mysql.jdbc.Driver
URL = jdbc:mysql:///mysql_jdbc
USER = root
PASSWORD = root
2.动态读取操作,只需要执行一次,所以解析代码写在静态代码块
Class.forName(),注册也只需要执行一次,所以也放在静态代码块
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("jdbc.properties"));
DRIVER_CLASS_NAME = properties.getProperty("DRIVER_CLASS_NAME");
URL = properties.getProperty("URL");
USER = properties.getProperty("USER");
PASSWORD = properties.getProperty("PASSWORD");
// System.out.println(DRIVER_CLASS_NAME+"-"+URL+"-"+USER+"-"+PASSWORD);
Class.forName(DRIVER_CLASS_NAME);
} catch (Exception e) {
e.printStackTrace();
}
}
3.为数据库的连接专门写个工具类用于获取连接和释放资源,以及公共代码
/*
工具类封装步骤:
1.jdbc中每次都要写注册获取连接和资源释放
2.参数数据,不应该直接写在代码中,直接抽取到最上面,便于后期开发维护
3.数据库连接四要素,不应该写死在代码中,扩展性不高,应该抽取到配置文件中动态读取
4.动态读取操作,只需要执行一次,所以解析代码写在静态代码块
5.class.forName(),注册也只需要执行一次,所以也放在静态代码块
*/
public class JdbcUtil {
public static String DRIVER_CLASS_NAME ;
public static String URL ;
public static String USER ;
public static String PASSWORD ;
//如果要保证一部分代码仅仅动态读取一次,则使用静态代码块
static {
try {
Properties properties = new Properties();
properties.load(new FileInputStream("jdbc.properties"));
DRIVER_CLASS_NAME = properties.getProperty("DRIVER_CLASS_NAME");
URL = properties.getProperty("URL");
USER = properties.getProperty("USER");
PASSWORD = properties.getProperty("PASSWORD");
// System.out.println(DRIVER_CLASS_NAME+"-"+URL+"-"+USER+"-"+PASSWORD);
Class.forName(DRIVER_CLASS_NAME);
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() {
Connection connection = null;
try {
//ctrl+Alt+C抽取
connection = DriverManager.getConnection(URL, USER,PASSWORD);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//资源释放
public static void release(ResultSet resultSet, Statement statement,Connection connection){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
resultSet = null;//避免内存泄漏
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
statement = null;//避免内存泄漏
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
connection = null;//避免内存泄漏
}
}
}
4.用户登录实例
package Login_01;
import java.sql.*;
import java.util.Scanner;
public class LoginDemo {
public static void main(String[] args) throws SQLException {
//提醒用户输入用户名
System.out.println("请输入用户名:");
Scanner scanner = new Scanner(System.in);
String username = scanner.nextLine();
System.out.println("请输入登录密码:");
Scanner scanner1 = new Scanner(System.in);
String password = scanner1.nextLine();
//提醒用户输入密码
Connection connection = JdbcUtil.getConnection();
Statement statement = connection.createStatement();
String sql = "SELECT count(*) FROM USER WHERE username = '"+username+"' AND password = '+password+' ;";
ResultSet resultSet = statement.executeQuery(sql);
int count = 0;
while (resultSet.next()) {
count = resultSet.getInt("count(*)");
}
System.out.println(count >0 ? "登录成功" :" 登录失败");
JdbcUtil.release(resultSet,statement,connection);
}
}
简单测试DemoTest:
import util.JDBCUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtilTest {
public static void main(String[] args) throws SQLException {
Connection connection = JDBCUtils.getConnection();
Statement statement = connection.createStatement();
String sql = "Update user set password = '54321'";
int i = statement.executeUpdate(sql);
System.out.println(i);
JDBCUtils.release(null,statement,connection);
}
}