黑马程序员技术交流社区

标题: 谁来讲下 jdbc的增删改查啊 [打印本页]

作者: 老骥伏枥 时间: 2012-12-8 11:47
标题: 谁来讲下 jdbc的增删改查啊
谁来讲下 jdbc的增删改查啊

作者: 邵新瑜 时间: 2012-12-8 11:57
举个实例给你看，应该就会明白

代码1  查询

import java.sql.*;

public class select {

   public static void main(String[] args) {

         Connection conn = null;    //定义变量

         PreparedStatement pstet =null;

         ResultSet rs = null;

         try {

               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //装在去动程序

            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=student","sa","123456"); //加载驱动并与数据库连接

            String sql = "select count(*) from  td_student"; //sql  语句

            pstet = conn.prepareStatement(sql); //发送sql语句并得到结果集

            rs = pstet.executeQuery();

            if (rs.next()){  // 判断数据集中是否有数据

               System.out.println("数据库中共有"+rs.getInt(1)+"条记录");

            }

         } catch (Exception e) {

               e.printStackTrace();

         }

         finally{

               clossConnection(conn);  //释放资源

               clossResultSet(rs);

               clossStatement(pstet);

         }

   }

   public static void clossConnection(Connection conn ){

         try {

               if(conn != null && !conn.isClosed()){

                  conn.close();

               }

         } catch (Exception e) {

               e.printStackTrace();

         }

   }

   public static void clossStatement(PreparedStatement pstet ){

         try {

               if(pstet != null) {

                  pstet.close();

               }

         } catch (Exception e) {

               e.printStackTrace();

         }

   }

   public static void clossResultSet(ResultSet rs ){

         try {

               if(rs != null) {

                  rs.close();

               }

         } catch (Exception e) {

               e.printStackTrace();

         }

   }

}

代码块2 增加（简化）

public static void main(String[] args) {

         Connection conn = null;    //定义变量

         PreparedStatement pstet =null;

         ResultSet rs = null;

         int num = 0;

         try {

               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //装在去动程序

            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=student","sa","123456"); //加载驱动并与数据库连接

            String sql = "insert into  td_student (Name,Score) values(?,?)"; //sql  语句  占位符的使用

            pstet = conn.prepareStatement(sql); //发送sql语句并得到结果集

            pstet.setString(1, "赵六");  // 1 表示第一个占位符

            pstet.setInt(2, 87);

            num = pstet.executeUpdate();

               System.out.println("共插入数据"+num+"条记录");

         } catch (Exception e) {

               e.printStackTrace();

         }

         finally{

               clossConnection(conn);  //释放资源

               clossResultSet(rs);

               clossStatement(pstet);

         }

   }

代码快3 删除（简化）

public static void main(String[] args) {

         Connection conn = null;    //定义变量

         PreparedStatement pstet =null;

         ResultSet rs = null;

         int num = 0;

         try {

               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //装在去动程序

            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=student","sa","123456"); //加载驱动并与数据库连接

            String sql = "delete from  td_student  where Name=? and Score=?"; //sql  语句  占位符的使用

            pstet = conn.prepareStatement(sql); //发送sql语句并得到结果集

            pstet.setString(1, "赵六");  // 1 表示第一个占位符

            pstet.setInt(2, 87);

            num = pstet.executeUpdate();

               System.out.println("共删除数据"+num+"条记录");

         } catch (Exception e) {

               e.printStackTrace();

         }

         finally{

               clossConnection(conn);  //释放资源

               clossResultSet(rs);

               clossStatement(pstet);

         }

   }

代码快4 修改

public static void main(String[] args) {

         Connection conn = null;    //定义变量

         PreparedStatement pstet =null;

         ResultSet rs = null;

         int num = 0;

         try {

               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); //装在去动程序

            conn = DriverManager.getConnection("jdbc:sqlserver://localhost:1433;databasename=student","sa","123456"); //加载驱动并与数据库连接

            String sql = "update td_student set Score=? where Name=?"; //sql  语句  占位符的使用

            pstet = conn.prepareStatement(sql); //发送sql语句并得到结果集

            pstet.setInt(1, 100); // 1 表示第一个占位符

            pstet.setString(2, "赵六");



            num = pstet.executeUpdate();

               System.out.println("共删除数据"+num+"条记录");

         } catch (Exception e) {

               e.printStackTrace();

         }

         finally{

               clossConnection(conn);  //释放资源

               clossResultSet(rs);

               clossStatement(pstet);

         }

   }

作者: 马海保 时间: 2012-12-8 12:00
本帖最后由冯海霞于 2012-12-9 20:50 编辑

Oracle使用JDBC进行增删改查
数据库和表
create table USERS
(
  USERNAME VARCHAR2(20) not null,
  PASSWORD VARCHAR2(20)
)
alter table USERS
  add constraint U_PK primary key (USERNAME)
/**
* JdbcExample.java
*
* Provider: CoderDream's Studio
*
* History
* Date(DD/MM/YYYY) Author       Description
* ----------------------------------------------------------------------------
* Apr 14, 2008             CoderDream       Created
*/

package com.coderdream.jdbc.oracle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
* @author XL
*
*/
public class JdbcExample {

private static Connection getConn() {
      String driver = "oracle.jdbc.driver.OracleDriver";
      String url = "jdbc:oracle:thin:@10.5.15.117:1521:csi";
      String username = "scott";
      String password = "tiger";
      Connection conn = null;
      try {
         Class.forName(driver);
         // new oracle.jdbc.driver.OracleDriver();
         conn = DriverManager.getConnection(url, username, password);
      }
      catch (ClassNotFoundException e) {
         e.printStackTrace();
      }
      catch (SQLException e) {
         e.printStackTrace();
      }

      return conn;
}

private static int insert(String username, String password) {
      Connection conn = getConn();
      int i = 0;
      String sql = "insert into users (username,password) values(?,?)";
      PreparedStatement pstmt;
      try {
         pstmt = conn.prepareStatement(sql);
         // Statement stat = conn.createStatement();
         pstmt.setString(1, username);
         pstmt.setString(2, password);
         i = pstmt.executeUpdate();
         System.out.println("resutl: " + i);

         pstmt.close();
         conn.close();
      }
      catch (SQLException e) {
         e.printStackTrace();
      }

      return i;
}

private static void query() {
      Connection conn = getConn();
      String sql = "select * from users";
      PreparedStatement pstmt;
      try {
         pstmt = conn.prepareStatement(sql);
         ResultSet rs = pstmt.executeQuery();
         while (rs.next()) {
            System.out.println("name: " + rs.getString("username")
                     + " \tpassword: " + rs.getString("password"));
         }

         rs.close();
         pstmt.close();
         conn.close();
      }
      catch (SQLException e) {
         e.printStackTrace();
      }

}

private static int update(String oldName, String newPass) {
      Connection conn = getConn();
      int i = 0;
      String sql = "update users set password='" + newPass
            + "' where username='" + oldName + "'";
      PreparedStatement pstmt;
      try {
         pstmt = conn.prepareStatement(sql);

         i = pstmt.executeUpdate();
         System.out.println("resutl: " + i);

         pstmt.close();
         conn.close();
      }
      catch (SQLException e) {
         e.printStackTrace();
      }

      return i;
}

private static int delete(String username) {
      Connection conn = getConn();
      int i = 0;
      String sql = "delete users where username='" + username + "'";
      PreparedStatement pstmt;
      try {
         pstmt = conn.prepareStatement(sql);

         i = pstmt.executeUpdate();
         System.out.println("resutl: " + i);

         pstmt.close();
         conn.close();
      }
      catch (SQLException e) {
         e.printStackTrace();
      }

      return i;
}

/**
   * @param args
   */
public static void main(String[] args) {
      insert("CDE", "123");
      insert("CoderDream", "456");
      query();
      update("CoderDream", "456");
      query();
      delete("CoderDream");
      query();
}
}

作者: 汪磊 时间: 2012-12-9 19:35
// 增加操作
3.       public void insert(Note note) throws Exception {
4.          String sql = "INSERT INTO note(id,title,author,content) VALUES(note_sequ.nextVal,?,?,?)" ;
5.          PreparedStatement pstmt = null ;
6.          DataBaseConnection dbc = null ;
7.          dbc = new DataBaseConnection() ;
8.          try {
9.             pstmt = dbc.getConnection().prepareStatement(sql) ;
10.             pstmt.setString(1,note.getTitle()) ;
11.             pstmt.setString(2,note.getAuthor()) ;
12.             pstmt.setString(3,note.getContent()) ;
13.             pstmt.executeUpdate() ;
14.             pstmt.close() ;
15.          } catch (Exception e) {
16.             // System.out.println(e) ;
17.             throw new Exception("操作中出现错误！！！") ;
18.          } finally {
19.             dbc.close() ;
20.          }
21.       }
// 修改操作
23.       public void update(Note note) throws Exception {
24.          String sql = "UPDATE note SET title=?,author=?,content=? WHERE id=?" ;
25.          PreparedStatement pstmt = null ;
26.          DataBaseConnection dbc = null ;
27.          dbc = new DataBaseConnection() ;
28.          try {
29.             pstmt = dbc.getConnection().prepareStatement(sql) ;
30.             pstmt.setString(1,note.getTitle()) ;
31.             pstmt.setString(2,note.getAuthor()) ;
32.             pstmt.setString(3,note.getContent()) ;
33.             pstmt.setInt(4,note.getId()) ;
34.             pstmt.executeUpdate() ;
35.             pstmt.close() ;
36.          } catch (Exception e) {
37.             throw new Exception("操作中出现错误！！！") ;
38.          } finally {
39.             dbc.close() ;
40.          }
41.       }
42.       // 删除操作
43.       public void delete(int id) throws Exception {
44.          String sql = "DELETE FROM note WHERE id=?" ;
45.          PreparedStatement pstmt = null ;
46.          DataBaseConnection dbc = null ;
47.          dbc = new DataBaseConnection() ;
48.          try {
49.             pstmt = dbc.getConnection().prepareStatement(sql) ;
50.             pstmt.setInt(1,id) ;
51.             pstmt.executeUpdate() ;
52.             pstmt.close() ;
53.          } catch (Exception e) {
54.             throw new Exception("操作中出现错误！！！") ;
55.          } finally {
56.             dbc.close() ;
57.          }
58.       }
59.       // 按ID查询，主要为更新使用
60.       public Note queryById(int id) throws Exception {
61.          Note note = null ;
62.          String sql = "SELECT id,title,author,content FROM note WHERE id=?" ;
63.          PreparedStatement pstmt = null ;
64.          DataBaseConnection dbc = null ;
65.          dbc = new DataBaseConnection() ;
66.          try {
67.             pstmt = dbc.getConnection().prepareStatement(sql) ;
68.             pstmt.setInt(1,id) ;
69.             ResultSet rs = pstmt.executeQuery() ;
70.             if(rs.next()) {
71.                   note = new Note() ;
72.                   note.setId(rs.getInt(1)) ;
73.                   note.setTitle(rs.getString(2)) ;
74.                   note.setAuthor(rs.getString(3)) ;
75.                   note.setContent(rs.getString(4)) ;
76.             }
77.             rs.close() ;
78.             pstmt.close() ;
79.          } catch (Exception e) {
80.             throw new Exception("操作中出现错误！！！") ;
81.          } finally {
82.             dbc.close() ;
83.          }
84.          return note ;
85.       }
86.       // 查询全部
87.       public List queryAll() throws Exception {
88.          List all = new ArrayList() ;
89.          String sql = "SELECT id,title,author,content FROM note" ;
90.          PreparedStatement pstmt = null ;
91.          DataBaseConnection dbc = null ;
92.          dbc = new DataBaseConnection() ;
93.          try {
94.             pstmt = dbc.getConnection().prepareStatement(sql) ;
95.             ResultSet rs = pstmt.executeQuery() ;
96.             while(rs.next()) {
97.                   Note note = new Note() ;
98.                   note.setId(rs.getInt(1)) ;
99.                   note.setTitle(rs.getString(2)) ;
100.                   note.setAuthor(rs.getString(3)) ;
101.                   note.setContent(rs.getString(4)) ;
102.                   all.add(note) ;
103.             }
104.             rs.close() ;
105.             pstmt.close() ;
106.          } catch (Exception e) {
107.             System.out.println(e) ;
108.             throw new Exception("操作中出现错误！！！") ;
109.          } finally {
110.             dbc.close() ;
111.          }
112.          return all ;
113.       }
114.       // 模糊查询
115.       public List queryByLike(String cond) throws Exception {
116.          List all = new ArrayList() ;
117.          String sql = "SELECT id,title,author,content FROM note WHERE title LIKE ? or AUTHOR LIKE ? or CONTENT LIKE ?" ;
118.          PreparedStatement pstmt = null ;
119.          DataBaseConnection dbc = null ;
120.          dbc = new DataBaseConnection() ;
121.          try {
122.             pstmt = dbc.getConnection().prepareStatement(sql) ;
123.             pstmt.setString(1,"%"+cond+"%") ;
124.             pstmt.setString(2,"%"+cond+"%") ;
125.             pstmt.setString(3,"%"+cond+"%") ;
126.             ResultSet rs = pstmt.executeQuery() ;
127.             while(rs.next()) {
128.                   Note note = new Note() ;
129.                   note.setId(rs.getInt(1)) ;
130.                   note.setTitle(rs.getString(2)) ;
131.                   note.setAuthor(rs.getString(3)) ;
132.                   note.setContent(rs.getString(4)) ;
133.                   all.add(note) ;
134.             }
135.             rs.close() ;
136.             pstmt.close() ;
137.          } catch (Exception e) {
138.             System.out.println(e) ;
139.             throw new Exception("操作中出现错误！！！") ;
140.          } finally {
141.             dbc.close() ;
142.          }
143.          return all ;
144.       }
145. };

作者: 彭小康 时间: 2012-12-10 15:01
本帖最后由彭小康于 2012-12-10 15:47 编辑

一直学习中。路过顶一下

欢迎光临黑马程序员技术交流社区 (http://bbs.itheima.com/)

黑马程序员IT技术论坛 X3.2