JDBC对数据库的增删查改

• 一.JDBC
• 二.JDBC访问数据库步骤
• 三.Statement与PreparedStatement区别
• 四.代码
• 1.Connection 方法
• 2.close() 静态方法
• 3.查找数据
• 4.增加数据
• 5.修改数据
• 6.删除数据
• 7.执行代码

一.JDBC

二.JDBC访问数据库步骤

1.CLass.forName()加载驱动
2.DriverManager获取Connection连接
3.创建Statement执行SQL语句
4.返回resultSet查询结果
5.close()释放资源

三.Statement与PreparedStatement区别

四.代码

1.Connection 方法

public static Connection getconn(){
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url,user,pwd);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

2.close() 静态方法

public static void close(Connection conn, PreparedStatement pst, ResultSet rs){
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pst!=null){
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

3.查找数据

public  class selectNews {
    public static ResultSet query(String sql,Object ...params){
      Connection conn= NewsDao.getconn();
        PreparedStatement pst=null;
        ResultSet rs=null;
        try {
            pst=conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
               pst.setObject(i+1,params[i]);
            }
            rs=pst.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
// NewsDao.close(conn,pst,null);
        }
        return rs;
    }

}

4.增加数据

public class addNews {
public  static  int add(String sql){
    Connection conn=NewsDao.getconn();
    try {
        Statement stmt=conn.createStatement();
        int num=stmt.executeUpdate(sql);
        return num;
    } catch (SQLException e) {
        e.printStackTrace();
    }finally {
        NewsDao.close(conn,null,null);
    }
    return -1;
}
}

5.修改数据

public class updateNews {
    public static int update(String sql,Object...params)
    {
        Connection conn=NewsDao.getconn();
        PreparedStatement pst=null;
        try {
            pst=conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
            return pst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        finally {
            NewsDao.close(conn,pst,null);
        }
        return -1;
    }
}

6.删除数据

public class deleteNews {
    public static int delete(String sql,Object...params){
        Connection conn=NewsDao.getconn();
        PreparedStatement pst=null;
        try {
             pst=conn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i+1,params[i]);
            }
            int i=pst.executeUpdate();
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            NewsDao.close(conn,pst,null);
        }return -1;
    }
}

7.执行代码

public class newsTest {

    @Test
    public void query() throws SQLException {
        String sql="select * from news_detail where 1=1";
        ResultSet rs=selectNews.query(sql);
        while (rs.next()){
            System.out.println( rs.getInt("id")+"---"+rs.getInt("categoryId") + "---"+rs.getString("title")
                    + "---"+rs.getString("summary") + "---"+rs.getString("content") + "---"+
                    rs.getString("picPath") + "---"+rs.getString("author") + "---"+
                    rs.getString("createDate") + "---"+rs.getString("modifyDate") );
        }
        rs.close();
    }
    @Test
    public void update(){
String sql="update news_detail set title=? where id=?";
int num=updateNews.update(sql,"大数据班开课了来个推荐呗",1);
        System.out.println(num>0?"更新成功":"更新失败");
    }
    @Test
    public void add(){
        String sql="insert into news_detail(categoryId,title," +
                "summary,content) values" +
                "(1,'寂寞的罗老师急需推荐,请帮帮他吧'," +
                "'罗老师准备了sql两千题报之以李'," +
                "'期待一个美妙的邂逅')";
        int num=addNews.add(sql);
        System.out.println(num>0?"插入成功":"插入失败");
    }
    @Test
    public void delete(){
        String sql="delete from news_detail where id=?";
        int num=deleteNews.delete(sql,4);
        System.out.println(num>0?"删除成功":"删除失败");
    }
}