使用JDBC操作数据库中数据的步骤
一：创建数据库中相关表对应的实体类，以User表为例
1.创建User.java文件，一般实体类放在pojo包下，对属性进行封装。对于int、double这样类型的数据，最好用包装类Integer，Double进行封装。
2.必须提供空参构造方法、必须提供相应的set和get方法。
3.酌情书写有参构造方法，改写toString()方法。
package com.liyang.pojo;
public class User {
private Integer id;
private String username;
private String password;
private Integer age;
//空参构造方法
public User(){}
public User(Integer id, String username, String password, Integer age) {
this.id = id;
this.username = username;
this.password = password;
this.age = age;
}
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;
}
public Integer getAge() {
return age;
}
public void setAge(Integer age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", age=" + age +
'}';
}
}
二、创建工厂类Factory以连接数据库，以MySQL5.6为例
1.在静态代码块中加载相应的数据库连接驱动（因为一般只加载一次）—Class.forName(“驱动程序”);
2.提供getCon()方法获取数据库连DriverManager.getConnection(“database”,”username”,”password”);
3.提供close()方法关闭数据库连接等，及时释放资源。
package com.liyang.factory;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Factory {
static{
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getCon(){
try {
return DriverManager.getConnection("jdbc:mysql:///你的数据库名","用户名","密码");//替换为自己的
} catch (Exception e) {
e.printStackTrace();
return null;
}
}
public static void close(ResultSet rs, Statement st, Connection con){
try{
if(rs != null){
rs.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(st != null){
st.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(con != null){
con.close();
}
}catch(Exception e){
e.printStackTrace();
}finally{
}
}
}
}
}
三、创建一个接口UserDaoIf，存放操作数据库中数据的未实现的方法。
package com.liyang.Dao;
import com.liyang.pojo.User;
import java.util.List;
public interface UserDaoIf {
//1:添加person
public boolean addUser(User u);
//2:根据id删除person
public boolean delUser(Integer id);
//3:根据name删除person
public boolean delUserByName(String username);
//4:拿取全部用户资料
public List<User> queryAll();
//5:根据用户名进行查重
public boolean checkName(String name);
//6:根据用户名和密码进行查询
public User queryByUsernameAndPassword(String username,String password);
//7:拿取总记录数
public Integer getCount();
//8:分页查询
public List<User> getPage(Integer index,Integer max);
//9:修改用户数据，两种方法
public boolean updateUserByStatement(User u);
public boolean updateUserByPreparedStatement(User u);
}
四、创建一个实现类UserDaoIml，继承UserDaoIf。
1.创建一个连接，两种类型的执行器，一个结果集供下面的方法使用。
2.实现未实现的方法，方法体内先书写SQL语句、在获取连接、获取执行器、获取SQL语句执行后的结果集，最后调用close方法关闭连接。 以上方法体内要进行异常处理
package com.liyang.Dao;
import com.liyang.factory.Factory;
import com.liyang.pojo.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class UserDaoImpl implements UserDaoIf{
Connection con;
Statement st;
PreparedStatement pst;
ResultSet rs;
@Override
public boolean addUser(User u) {
try{
String sql = "insert into User values(null,?,?,?)";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,u.getUsername());
pst.setString(2,u.getPassword());
pst.setInt(3,u.getAge());
return pst.executeUpdate() == 1;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(null,pst,con);
}
}
@Override
public boolean delUser(Integer id) {
try{
String sql = "delete from User where id = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setInt(1,id);
return pst.executeUpdate() == 1;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(null,pst,con);
}
}
@Override
public boolean delUserByName(String username) {
try{
String sql = "delete from User where username = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,username);
return pst.executeUpdate() == 1;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(null,pst,con);
}
}
@Override
public List<User> queryAll() {
try{
String sql = "select * from User";
con = Factory.getCon();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
List<User> list = new ArrayList<>();
while(rs.next()){
list.add(new User(rs.getInt(1)
,rs.getString(2)
,rs.getString(3)
,rs.getInt(4)));
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
Factory.close(rs,pst,con);
}
}
@Override
public boolean checkName(String name) {
try{
String sql = "select * from User where username = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,name);
rs = pst.executeQuery();
if(rs.next()){
return true;
}
return false;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(null,pst,con);
}
}
@Override
public User queryByUsernameAndPassword(String username, String password) {
try{
String sql = "select * from User where username = ? and password = ?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,username);
pst.setString(2,password);
rs = pst.executeQuery();
if(rs.next()){
return new User(rs.getInt(1)
,rs.getString(2)
,rs.getString(3)
,rs.getInt(4));
}
return null;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
Factory.close(rs,pst,con);
}
}
@Override
public Integer getCount() {
try{
String sql = "select count(*) from User";
con = Factory.getCon();
pst = con.prepareStatement(sql);
rs = pst.executeQuery();
rs.next();
return rs.getInt(1);
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
Factory.close(rs,pst,con);
}
}
@Override
public List<User> getPage(Integer index, Integer max) {
try{
String sql = "Select * from User limit ?,?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setInt(1,index);
pst.setInt(2,max);
rs = pst.executeQuery();
List<User> list = new ArrayList<>();
while(rs.next()){
list.add(new User(rs.getInt(1)
,rs.getString(2)
,rs.getString(3)
,rs.getInt(4)));
}
return list;
}catch(Exception e){
e.printStackTrace();
return null;
}finally{
Factory.close(rs,pst,con);
}
}
@Override
public boolean updateUserByStatement(User u) {
try{
String sql ="update User set ";
if(u.getUsername() != null){
sql += "username = '" + u.getUsername() + "',";
}
if(u.getPassword() != null){
sql += "password = '" + u.getPassword() + "',";
}
if(u.getAge() != null){
sql += "age = " + u.getAge() + ",";
}
sql = sql.substring(0,sql.length() - 1);
sql += " where id =" + u.getId();
con = Factory.getCon();
st = con.createStatement();
return st.executeUpdate(sql) == 1;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(rs,st,con);
}
}
@Override
public boolean updateUserByPreparedStatement(User u) {
try{
String sql = "update User set username = ?,password = ?,age =? where id =?";
con = Factory.getCon();
pst = con.prepareStatement(sql);
pst.setString(1,u.getUsername());
pst.setString(2,u.getPassword());
pst.setInt(3,u.getAge());
pst.setInt(4,u.getId());
return pst.executeUpdate() == 1;
}catch(Exception e){
e.printStackTrace();
return false;
}finally{
Factory.close(rs,pst,con);
}
}
}
五、创建一个测试类，该类用来测试UserDaoIml类中的方法是否正确
1.要创建一个UserDaoIml类的对象，用来调用相关方法
2.创建实体类User的对象，向数据库中添加元素以及列出表中信息等情况下是会用到。
package com.liyang.test;
import com.liyang.Dao.UserDaoIf;
import com.liyang.Dao.UserDaoImpl;
import com.liyang.pojo.User;
public class TestDao {
public static void main(String[] args) {
UserDaoIf dao = new UserDaoImpl();
User u = new User(8,"Derek","young",23);
System.out.println(dao.addUser(u));
System.out.println(dao.delUser(6));
System.out.println(dao.delUserByName("Derek"));
for(User us : dao.queryAll()){
System.out.println(us);
}
System.out.println(dao.checkName("Amy"));
System.out.println(dao.queryByUsernameAndPassword("Raj","123456789"));
System.out.println(dao.getCount());
for(User us : dao.getPage(1,3)){
System.out.println(us);
}
System.out.println(dao.updateUserByStatement(u));
}
}
扫一扫
3951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
