操作数据库的基本步骤
1.0 原始版本
-
public static void main(String[] args) throws SQLException {
// 创建驱动对象
Driver driver = new Driver();
// 注册驱动
DriverManager.registerDriver(driver);
/**
* 获取连接 url user password http://www.baidu.com/login.html
* url: 数据库的通信地址 jdbc:mysql://localhost:3306/java2216
* user： 数据的用户名 root
* password： 用户密码 root
*/
String url = "jdbc:mysql://localhost:3306/java2216?useSSL=false";
String username = "root";
String password = "root";
//获取数据库的连接
Connection connection = DriverManager.getConnection(url, username, password);
System.out.println("connection = " + connection);
// 通过连接获取发送sql语句的对象 Statement
Statement statement = connection.createStatement();
//编写sql
//String sql = "SELECT * FROM user WHERE id = 1";
String sql = "SELECT * FROM user ";
// 发送sql语句到mysql数据库
ResultSet resultSet = statement.executeQuery(sql);
// 处理结果集 resultSet resultSet.next() 查询数据结果集中是否还有下一条数据
// 创建集合 存储查询结果中封装的数据
ArrayList<User> users = new ArrayList<>();
while (resultSet.next()){
//获取当前记录中字段值 根据字段值的数据类型获取
int id = resultSet.getInt("id"); // 通过字段名获取
String uname = resultSet.getString(2); // 通过字段的列索引
String pwd = resultSet.getString("password");
User user = new User().setId(id).setPassword(pwd).setUsername(uname);
users.add(user);
//System.out.println("user = " + user);
}
// 遍历集合
//users.forEach(user-> System.out.println("user = " + user));
for (User user : users) {
System.out.println(user);
}
// 释放资源
resultSet.close();
statement.close();
connection.close();
}
}
2.0 封装JDBCUtil工具类（快速的获取连接 和释放资源）
-
public class JDBCUtil {
private JDBCUtil(){} //防止被外部类new一个JDBCUtil
private static String username;
private static String driverClassName;
private static String password;
private static String url;
static { // 执行一次 类加载时
ResourceBundle file = ResourceBundle.getBundle("jdbc");
driverClassName = file.getString("driverClassName");
url = file.getString("url");
username = file.getString("username");
password = file.getString("password");
}
//获取数据库连接的方法
public static Connection getConnection(){
//通过反射注册驱动
Connection connection = null;
try {
Class.forName(driverClassName);
connection = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return connection;
}
//释放资源
public static void closeAll(Connection connection, Statement statement, ResultSet resultSet){
if(resultSet!=null){
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(statement!=null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
-
模拟用户登录和注册
public class UserService {
// 用户登录
public Boolean login(User user){
//根据用户信息 查询数据库 1.数据库中存在用户信息 登录成功 jdbc查询数据库
// 获取连接
Connection connection = JDBCUtil.getConnection();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
String sql = "select * from user where username= '"+user.getUsername()+"' and password = '"+user.getPassword()+"'";
System.out.println("sql = " + sql);
resultSet = statement.executeQuery(sql);
return resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtil.closeAll(connection, statement, resultSet);
}
return false;
}
// 用户注册
public Boolean register(User user){
Connection connection = JDBCUtil.getConnection();
try {
Statement statement = connection.createStatement();
String sql = "insert into user(username,password)values('"+user.getUsername()+"','"+user.getPassword()+"')";
System.out.println("sql = " + sql);
int i = statement.executeUpdate(sql);
return i>0;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
3.0 解决sql注入
-
创建PreparedStatement 对象发送sql语句解决sql注入问题
@Test
public void test(){
Connection connection = JDBCUtil.getConnection();
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
// 使用 ？ 进行参数的占位
String sql = "select * from user where username = ? and password = ? " ;
//创建PreparedStatement 对象时 需要传递sql语句 因为该对象会将sql语句进行预编译
try {
preparedStatement = connection.prepareStatement(sql);
// 在preparedStatement 执行sql语句之前 需要将sql语句中的参数设置好
preparedStatement.setString(1, "zhangsan");
preparedStatement.setString(2, "8888");
//通过 preparedStatement对象 执行sql语句
resultSet = preparedStatement.executeQuery();
User user = null;
while(resultSet.next()){
String username = resultSet.getString(2);
String password = resultSet.getString(3);
user = new User().setPassword(password).setUsername(username);
}
System.out.println("user = " + user);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JDBCUtil.closeAll(connection,preparedStatement,resultSet);
}
}
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
