import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/*DAO--DataBase Access Object 用于访问数据库的对象,直接操作Userbean,
* DAO 利用PreparedStatement的对象来执行SQL语句,所以DAO是利用PreparedStatement来直接访问数据库,
* DAO中包含了三个类,分别是Connection(负责连接数据库),PrepareStatement(负责执行SQL语句),
* ResultSet(负责保存SQL语句的查询会更新结果)*/
public class UserDao
{//A connection (session) with a specific database. SQL statements are executed and results are returned within the context of a connection.
// Java.sql.Conection是一个抽象的接口,用于规范、并和指定的数据库建立连接,具体的实现有各个数据库厂商提供
//JDK中并没有给出具体实现
public Connection getConnection() throws Exception
{
Class.forName("com.mysql.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/db18class";
String user="root";
String password="123456";
Connection connection=DriverManager.getConnection(url,user,password);
return connection;
}
public boolean add(String name,String password)
{
Connection connection =null;
PreparedStatement preparedStatement=null;
try {
//获取数据库连接
connection=getConnection();
String sql="insert into users(name,password)values(?,?)";
//使得SQL语句和数据库连接,准备好执行SQL语句
preparedStatement=connection.prepareStatement(sql);
//Sets the designated parameter to the given Java String value.
//将name中的值赋值给parameterIndex为1的变量,这里的索引序列,有SQL语句中的顺序决定
preparedStatement.setString(1,name);
preparedStatement.setString(2, password);
//执行SQL语句
preparedStatement.execute();
return true;
} catch (Exception e) {}
finally{
try {preparedStatement.close();} catch (SQLException e) {}
try {connection.close();} catch (SQLException e) {}
}
return false;
}
public UserBean findById(int id)
{
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection =getConnection();
String sql="select id ,name,password from users where id=?";
//
preparedStatement=connection.prepareStatement(sql);
//将id的值赋值给parameterIndex为1的变量
preparedStatement.setInt(1,id);
resultSet=preparedStatement.executeQuery();
if(resultSet.next())
{
UserBean userBean=new UserBean();
userBean.setId(resultSet.getInt(1));
userBean.setName(resultSet.getString(2));
userBean.setPassword(resultSet.getString(3));
return userBean;
}
} catch (Exception e) {}
finally{
try {resultSet.close();} catch (SQLException e) {}
try {preparedStatement.close();} catch (SQLException e) {}
try {connection.close();} catch (SQLException e) {}
}
return null;
}
public List<UserBean>findAll()
{
List<UserBean>list=new ArrayList<UserBean>();
Connection connection=null;
PreparedStatement preparedStatement=null;
ResultSet resultSet=null;
try {
connection =getConnection();
String sql="select id,name,password from users";
preparedStatement=connection.prepareStatement(sql);
resultSet=preparedStatement.executeQuery();
while(resultSet.next())
{
UserBean userBean=new UserBean();
userBean.setId(resultSet.getInt(1));
userBean.setName(resultSet.getString(2));
userBean.setPassword(resultSet.getString(3));
list.add(userBean);
}
return list;
} catch (Exception e) {}
finally{
try {resultSet.close();} catch (SQLException e) {}
try {preparedStatement.close();} catch (SQLException e) {}
try {connection.close();} catch (SQLException e) {}
}
return null;
}
}