1.建立如图所示的目录架构:
2.dao层用来对数据库进行操作,有一个接口UserDao和一个接口实现类UserDaoImpl组成
UserDao的代码:
package com.unicom.dao;
import com.unicom.domain.User;
public interface UserDao {
/*根据用户名与密码获取一个用户*/
User getUserWithUserNameAndPassword(String username,String password);
}
UserDaoImpl的代码实现:
package com.unicom.dao.impl;
import com.unicom.dao.UserDao;
import com.unicom.domain.User;
import com.unicom.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/*准备工作:
* 1.导入mysql和druid的jar包 放到web-inf/lib 进行添加
* 2.在工程当中创建一个com.unicom.utils包把JdbcUtils工具类拷贝到该包
* 3.把db.properties配置文件拷贝到src包当中
* */
public class UserDaoImpl implements UserDao {
@Override
public User getUserWithUserNameAndPassword(String username,String password) {
Connection connection = null;
//准备执行语句
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
获取数据库连接对象
connection = JDBCUtils.getConnection();
String sql = "select * from user where username=? and password=?";
preparedStatement = connection.prepareStatement(sql);
//执行语句之前设置语句参数
preparedStatement.setString(1,username);
preparedStatement.setString(2,password);
//执行语句
resultSet = preparedStatement.executeQuery();
// 从结果集当中 获取内容
if (resultSet.next()){
User user = new User();
user.setId(resultSet.getInt("id"));
user.setUsername(resultSet.getString("username"));
user.setPassword(resultSet.getString("password"));
return user;
}else {
return null;
}
} catch (SQLException e) {
e.printStackTrace();
return null;
}finally {
//释放资源
JDBCUtils.close(resultSet,preparedStatement,connection);
}
}
}
2.domain主要用来装入数据库表的实体(关系,一行数据)
package com.unicom.domain;
public class User {
Integer id;
String username;
String password;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
3.utils工具类(用来存放常用的工具,代码固定)
package com.unicom.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
web工程
src 所有的java文件
在程序运行时, 会把所有的src中java文件编译成字节码 放到运行目录当中 WEB-INF/classes
*/
public class JDBCUtils {
private JDBCUtils(){}
public static Connection getConnection(){
try {
Properties properties = new Properties();
/*通过类加载器来获取资源文件*/
InputStream resourceAsStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(resourceAsStream);
DataSource ds = DruidDataSourceFactory.createDataSource(properties);
Connection connection = ds.getConnection();
return connection;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**关闭资源*/
public static void close(ResultSet rs, Statement st, Connection conn){
if (rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
db.properties 为资源文件(在new-->File-->resource bundel)
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/gxq_db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false
username=root
password=123456
maxActive=8