Java连接数据库，实现“增、删、改、查”的基本操作

来源：互联网发布：网络biu biu什么意思编辑：程序博客网时间：2024/06/11 04:16

package com.imooc.db;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBUtil {    private static final String URL="jdbc:mysql://127.0.0.1:3306/imooc?useUnicode=true&amp;characterEncoding=utf-8";    private static final String USER="root";    private static final String PASSWORD="";    private static Connection conn=null;    static {        try {            //1.加载驱动程序            Class.forName("com.mysql.jdbc.Driver");            //2.获得数据库的连接            conn=DriverManager.getConnection(URL, USER, PASSWORD);        } catch (ClassNotFoundException e) {            e.printStackTrace();        } catch (SQLException e) {            e.printStackTrace();        }    }    public static Connection getConnection(){        return conn;    }}

package com.imooc.model;import java.util.Date;public class Goddess {    private Integer id;    private String user_name;    private Integer sex;    private Integer age;    private Date birthday;    private String email;    private String mobile;    private String create_user;    private String update_user;    private Date create_date;    private Date update_date;    private Integer isdel;    public Integer getId() {        return id;    }    public void setId(Integer id) {        this.id = id;    }    public String getUser_name() {        return user_name;    }    public void setUser_name(String user_name) {        this.user_name = user_name;    }    public Integer getSex() {        return sex;    }    public void setSex(Integer sex) {        this.sex = sex;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }    public Date getBirthday() {        return birthday;    }    public void setBirthday(Date birthday) {        this.birthday = birthday;    }    public String getEmail() {        return email;    }    public void setEmail(String email) {        this.email = email;    }    public String getMobile() {        return mobile;    }    public void setMobile(String mobile) {        this.mobile = mobile;    }    public String getCreate_user() {        return create_user;    }    public void setCreate_user(String create_user) {        this.create_user = create_user;    }    public String getUpdate_user() {        return update_user;    }    public void setUpdate_user(String update_user) {        this.update_user = update_user;    }    public Date getCreate_date() {        return create_date;    }    public void setCreate_date(Date create_date) {        this.create_date = create_date;    }    public Date getUpdate_date() {        return update_date;    }    public void setUpdate_date(Date update_date) {        this.update_date = update_date;    }    public Integer getIsdel() {        return isdel;    }    public void setIsdel(Integer isdel) {        this.isdel = isdel;    }    @Override    public String toString() {        return "Goddess [id=" + id + ", user_name=" + user_name + ", sex="                + sex + ", age=" + age + ", birthday=" + birthday + ", email="                + email + ", mobile=" + mobile + ", create_user=" + create_user                + ", update_user=" + update_user + ", create_date="                + create_date + ", update_date=" + update_date + ", isdel="                + isdel + "]";    }}

 package com.imooc.dao;import java.sql.Connection;import java.sql.Date;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import com.imooc.db.DBUtil;import com.imooc.model.Goddess;public class GoddessDao {    public void addGoddess(Goddess g) throws Exception{        Connection conn=DBUtil.getConnection();        String sql="" +                "insert into imooc_goddess" +                "(user_name,sex,age,birthday,email,mobile," +                "create_user,create_date,update_user,update_date,isdel)" +                "values(" +                "?,?,?,?,?,?,?,current_date(),?,current_date(),?)";        PreparedStatement ptmt=conn.prepareStatement(sql);        ptmt.setString(1, g.getUser_name());        ptmt.setInt(2, g.getSex());        ptmt.setInt(3, g.getAge());        ptmt.setDate(4, new Date(g.getBirthday().getTime()));        ptmt.setString(5, g.getEmail());        ptmt.setString(6, g.getMobile());        ptmt.setString(7, g.getCreate_user());        ptmt.setString(8, g.getUpdate_user());        ptmt.setInt(9, g.getIsdel());        ptmt.execute();    }    public void updateGoddess(Goddess g) throws SQLException{        Connection conn=DBUtil.getConnection();        String sql="" +                " update imooc_goddess " +                " set user_name=?,sex=?,age=?,birthday=?,email=?,mobile=?, " +                " update_user=?,update_date=current_date(),isdel=? " +                " where id=? ";        PreparedStatement ptmt=conn.prepareStatement(sql);        ptmt.setString(1, g.getUser_name());        ptmt.setInt(2, g.getSex());        ptmt.setInt(3, g.getAge());        ptmt.setDate(4, new Date(g.getBirthday().getTime()));        ptmt.setString(5, g.getEmail());        ptmt.setString(6, g.getMobile());        ptmt.setString(7, g.getUpdate_user());        ptmt.setInt(8, g.getIsdel());        ptmt.setInt(9, g.getId());        ptmt.execute();    }    public void delGoddess(Integer id) throws SQLException{        Connection conn=DBUtil.getConnection();        String sql="" +                " delete from imooc_goddess " +                " where id=? ";        PreparedStatement ptmt=conn.prepareStatement(sql);        ptmt.setInt(1, id);        ptmt.execute();    }    public List<Goddess> query() throws Exception{        List<Goddess> result=new ArrayList<Goddess>();        Connection conn=DBUtil.getConnection();        StringBuilder sb=new StringBuilder();        sb.append("select id,user_name,age from imooc_goddess  ");        PreparedStatement ptmt=conn.prepareStatement(sb.toString());        ResultSet rs=ptmt.executeQuery();        Goddess g=null;        while(rs.next()){            g=new Goddess();            g.setId(rs.getInt("id"));            g.setUser_name(rs.getString("user_name"));            g.setAge(rs.getInt("age"));            result.add(g);        }        return result;    }    public List<Goddess> query(String name,String mobile,String email) throws Exception{        List<Goddess> result=new ArrayList<Goddess>();        Connection conn=DBUtil.getConnection();        StringBuilder sb=new StringBuilder();        sb.append("select * from imooc_goddess  ");        sb.append(" where user_name like ? and mobile like ? and email like ?");        PreparedStatement ptmt=conn.prepareStatement(sb.toString());        ptmt.setString(1, "%"+name+"%");        ptmt.setString(2, "%"+mobile+"%");        ptmt.setString(3, "%"+email+"%");        System.out.println(sb.toString());        ResultSet rs=ptmt.executeQuery();        Goddess g=null;        while(rs.next()){            g=new Goddess();            g.setId(rs.getInt("id"));            g.setUser_name(rs.getString("user_name"));            g.setAge(rs.getInt("age"));            g.setSex(rs.getInt("sex"));            g.setBirthday(rs.getDate("birthday"));            g.setEmail(rs.getString("email"));            g.setMobile(rs.getString("mobile"));            g.setCreate_date(rs.getDate("create_date"));            g.setCreate_user(rs.getString("create_user"));            g.setUpdate_date(rs.getDate("update_date"));            g.setUpdate_user(rs.getString("update_user"));            g.setIsdel(rs.getInt("isdel"));            result.add(g);        }        return result;    }    public List<Goddess> query(List<Map<String, Object>> params) throws Exception{        List<Goddess> result=new ArrayList<Goddess>();        Connection conn=DBUtil.getConnection();        StringBuilder sb=new StringBuilder();        sb.append("select * from imooc_goddess where 1=1 ");        if(params!=null&&params.size()>0){            for (int i = 0; i < params.size(); i++) {                Map<String, Object> map=params.get(i);                sb.append(" and  "+map.get("name")+" "+map.get("rela")+" "+map.get("value")+" ");            }        }        PreparedStatement ptmt=conn.prepareStatement(sb.toString());        System.out.println(sb.toString());        ResultSet rs=ptmt.executeQuery();        Goddess g=null;        while(rs.next()){            g=new Goddess();            g.setId(rs.getInt("id"));            g.setUser_name(rs.getString("user_name"));            g.setAge(rs.getInt("age"));            g.setSex(rs.getInt("sex"));            g.setBirthday(rs.getDate("birthday"));            g.setEmail(rs.getString("email"));            g.setMobile(rs.getString("mobile"));            g.setCreate_date(rs.getDate("create_date"));            g.setCreate_user(rs.getString("create_user"));            g.setUpdate_date(rs.getDate("update_date"));            g.setUpdate_user(rs.getString("update_user"));            g.setIsdel(rs.getInt("isdel"));            result.add(g);        }        return result;    }    public Goddess get(Integer id) throws SQLException{        Goddess g=null;        Connection conn=DBUtil.getConnection();        String sql="" +                " select * from imooc_goddess " +                " where id=? ";        PreparedStatement ptmt=conn.prepareStatement(sql);        ptmt.setInt(1, id);        ResultSet rs=ptmt.executeQuery();        while(rs.next()){            g=new Goddess();            g.setId(rs.getInt("id"));            g.setUser_name(rs.getString("user_name"));            g.setAge(rs.getInt("age"));            g.setSex(rs.getInt("sex"));            g.setBirthday(rs.getDate("birthday"));            g.setEmail(rs.getString("email"));            g.setMobile(rs.getString("mobile"));            g.setCreate_date(rs.getDate("create_date"));            g.setCreate_user(rs.getString("create_user"));            g.setUpdate_date(rs.getDate("update_date"));            g.setUpdate_user(rs.getString("update_user"));            g.setIsdel(rs.getInt("isdel"));        }        return g;    }}

0 0