java sql: PrepareStatement

无情 阅读:1023 2020-03-14 13:32:58 评论:0

增删改查:

public class DbUtil { 
    public static final String URL = "jdbc:mysql://localhost:3306/imooc"; 
    public static final String USER = "liulx"; 
    public static final String PASSWORD = "123456"; 
    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 liulx.model; 
 
import java.util.Date; 
 
public class Goddess { 
 
    private Integer id; 
    private String user_name; 
    private Integer sex; 
    private Integer age; 
    private Date birthday; //注意用的是java.util.Date 
    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; 
    //getter setter方法。。。 
} 
 
//---------dao层-------------- 
package liulx.dao; 
 
import liulx.db.DbUtil; 
import liulx.model.Goddess; 
 
import java.sql.Connection; 
import java.sql.ResultSet; 
import java.sql.SQLException; 
import java.sql.Statement; 
import java.util.ArrayList; 
import java.util.List; 
 
public class GoddessDao { 
    //增加 
    public void addGoddess(Goddess g) throws SQLException { 
        //获取连接 
        Connection conn = DbUtil.getConnection(); 
        //sql 
        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); //预编译SQL,减少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(){ 
        //获取连接 
        Connection conn = DbUtil.getConnection(); 
        //sql, 每行加空格 
        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); //预编译SQL,减少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(){ 
        //获取连接 
        Connection conn = DbUtil.getConnection(); 
        //sql, 每行加空格 
        String sql = "delete from imooc_goddess where id=?"; 
        //预编译SQL,减少sql执行 
        PreparedStatement ptmt = conn.prepareStatement(sql); 
 
        //传参 
        ptmt.setInt(1, id); 
 
        //执行 
        ptmt.execute(); 
    } 
 
    public List<Goddess> query() throws SQLException { 
        Connection conn = DbUtil.getConnection(); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery("SELECT user_name, age FROM imooc_goddess"); 
 
        List<Goddess> gs = new ArrayList<Goddess>(); 
        Goddess g = null; 
        while(rs.next()){ 
            g = new Goddess(); 
            g.setUser_name(rs.getString("user_name")); 
            g.setAge(rs.getInt("age")); 
 
            gs.add(g); 
        } 
        return gs; 
    } 
 
    public Goddess get(){ 
        Goddess g = null; 
        //获取连接 
        Connection conn = DbUtil.getConnection(); 
        //sql, 每行加空格 
        String sql = "select * from  imooc_goddess where id=?"; 
        //预编译SQL,减少sql执行 
        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; 
    } 
}

 

标签:数据库java
声明

1.本站遵循行业规范,任何转载的稿件都会明确标注作者和来源;2.本站的原创文章,请转载时务必注明文章作者和来源,不尊重原创的行为我们将追究责任;3.作者投稿可能会经我们编辑修改或补充。

关注我们

一个IT知识分享的公众号