pom依賴:
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <groupId>com.changping.jdbcdemo</groupId> <artifactId>jdbcdemo</artifactId> <version>0.0.1-SNAPSHOT</version> <dependencies> <dependency> <groupId>oracle</groupId> <artifactId>ojdbc6-connector-java</artifactId> <version>10.2.0.1.0</version> </dependency> </dependencies> </project>
pojo.User.java
package com.changping.jdbc.pojo; public class User { private String userid; private String userpass; private String username; private String db_source; public String getUserid() { return userid; } public void setUserid(String userid) { this.userid = userid; } public String getUserpass() { return userpass; } public void setUserpass(String userpass) { this.userpass = userpass; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getDb_source() { return db_source; } public void setDb_source(String db_source) { this.db_source = db_source; } @Override public String toString() { return "User [userid=" + userid + ", userpass=" + userpass + ", username=" + username + ", db_source=" + db_source + "]"; } public User(String userid, String userpass, String username, String db_source) { super(); this.userid = userid; this.userpass = userpass; this.username = username; this.db_source = db_source; } public User(String userpass, String username, String db_source) { super(); this.userpass = userpass; this.username = username; this.db_source = db_source; } public User() { super(); } }
dao.UserDao.java
package com.changping.jdbc.dao; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.changping.jdbc.pojo.User; public class UserDao { /** * 1.查全部 * * @return */ public List<Map<String, Object>> queryUser() { List<Map<String, Object>> list_user = new ArrayList<Map<String, Object>>(); // 1声明jdbc对象 Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 2加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); // 3声明连接信息 String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; String username = "scott"; String password = "tiger"; // 4建立连接 try { conn = DriverManager.getConnection(url, username, password); // 5声明sql语句 String sql = "select * from db_user"; // 6声明预编译对象 pstmt = conn.prepareStatement(sql); // 7返回resultset结果集 rs = pstmt.executeQuery(); // 8得到结果集元数据——方法的集合体,返回有column相关的一切信息 ResultSetMetaData rsmd = rs.getMetaData(); // 9得到结果集总列数 int columnCount = rsmd.getColumnCount(); // 10通过结果集列数进行遍历 while (rs.next()) { Map<String, Object> map = new HashMap<String, Object>(); // 遍历每一列并放入map中 for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { String columnName = rsmd.getColumnName(columnIndex); Object value = rs.getObject(columnName); map.put(columnName, value); } list_user.add(map); } } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } return list_user; } /** * 2.增 * * @param user * @return */ public int add_User(User user) { // 声明计数器 int result = 0; // 1声明连接信息 Connection conn = null; PreparedStatement pstmt = null; // 2加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); // 3声明连接信息 String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; String username = "scott"; String password = "tiger"; // 4建立连接 try { conn = DriverManager.getConnection(url, username, password); // 5声明sql语句 String sql = "insert into db_user (userid,userpass,username,db_source) values (seq_new_user.nextval,?,?,?)"; // 6执行预编译sql,给参数赋值 pstmt = conn.prepareStatement(sql); pstmt.setObject(1, user.getUserpass()); pstmt.setObject(2, user.getUsername()); pstmt.setObject(3, user.getDb_source()); // 7执行sql,返回受影响的条目数 result = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (Exception e) { e.printStackTrace(); } } return result; } /** * 3.根据姓名查询 * * @param username * @return */ public Map<String, Object> find_User(String username) { Map<String, Object> user_map = new HashMap<String, Object>(); // 1声明jdbc对象 Connection conn = null; PreparedStatement pstmt = null; ResultSet rs = null; // 2加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); // 3声明连接信息 String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; String user = "scott"; String password = "tiger"; // 4建立连接 try { conn = DriverManager.getConnection(url, user, password); // 5声明sql语句 String sql = "select * from db_user where username = ?"; // 6执行预编译sql,给参数赋值 pstmt = conn.prepareStatement(sql); pstmt.setObject(1, username); // 7返回结果集信息 rs = pstmt.executeQuery(); // 8得到结果集元数据——方法的集合体,返回有column相关的一切信息 ResultSetMetaData rsmd = rs.getMetaData(); // 9从元数据类中返回结果集的列数 int columnCount = rsmd.getColumnCount(); // 10根据元数据里的列信息,得到每一类的字段名 if (rs.next()) { for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) { String columnName = rsmd.getColumnName(columnIndex);// 根据对应列,返回列名 Object value = rs.getObject(columnName);// 根据列名返回该列所有值 user_map.put(columnName, value); } } } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (rs != null) { rs.close(); } if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } return user_map; } /** * 4.通过姓名修改 * * @param user * @return */ public int update_user_by_username(User user) { int result = 0; // 1声明jdbc对象 Connection conn = null; PreparedStatement pstmt = null; // 2加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); // 3声明连接信息 String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; String username = "scott"; String password = "tiger"; // 4建立连接 try { conn = DriverManager.getConnection(url, username, password); // 5声明sql语句 String sql = "update db_user set userpass=?,db_source=? where username=?"; // 6执行预编译sql,给参数赋值 pstmt = conn.prepareStatement(sql); pstmt.setObject(1, user.getUserpass()); pstmt.setObject(2, user.getDb_source()); pstmt.setObject(3, user.getUsername()); // 7返回受影响的条目数 result = pstmt.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } } catch (ClassNotFoundException e) { e.printStackTrace(); } finally { try { if (pstmt != null) { pstmt.close(); } if (conn != null) { conn.close(); } } catch (SQLException e) { e.printStackTrace(); } } return result; } /** * 5.通过姓名删除 * * @param username * @return */ public int delete_user_by_username(String username) { int result = 0; // 1.声明jdbc对象 Connection conn = null; PreparedStatement pstmt = null; // 2加载驱动 try { Class.forName("oracle.jdbc.driver.OracleDriver"); // 3声明连接信息 String url = "jdbc:oracle:thin:@localhost:1521:ORCL"; String user = "scott"; String password = "tiger"; // 4建立连接 conn = DriverManager.getConnection(url, user, password); // 5声明sql语句 String sql = "delete from db_user where username=?"; // 6执行预编译sql,给参数赋值 pstmt = conn.prepareStatement(sql); pstmt.setObject(1, username); // 7返回受影响的条目数 result = pstmt.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { try { if (conn != null) { conn.close(); } if (pstmt != null) { pstmt.close(); } } catch (SQLException e) { e.printStackTrace(); } } return result; } }
test.User_Add_Test.java
package com.changping.jdbc.test; import com.changping.jdbc.dao.UserDao; import com.changping.jdbc.pojo.User; public class User_Add_Test { public static void main(String[] args) { UserDao userDao = new UserDao(); User user = new User("123","jerry","db_oracle_0424");//三参构造 int result = userDao.add_User(user);//return 1 System.out.println(result); } }
test.User_Delete_Test.java
package com.changping.jdbc.test; import com.changping.jdbc.dao.UserDao; public class User_Delete_Test { public static void main(String[] args) { UserDao userDao = new UserDao(); int result = userDao.delete_user_by_username("tom"); System.out.println(result); } }
test.User_Find_by_Name_Test.java
package com.changping.jdbc.test; import java.util.ArrayList; import java.util.Iterator; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import com.changping.jdbc.dao.UserDao; public class User_Find_by_Name_Test { public static void main(String[] args) { UserDao userDao = new UserDao(); Map<String, Object> user_map = userDao.find_User("tom"); // foreach遍历 Set<Entry<String, Object>> entryset = (Set<Entry<String, Object>>) user_map.entrySet(); for (Entry<String, Object> entry : entryset) { System.out.println(entry.getKey() + " : " + entry.getValue()); } System.out.println("------------------------------------------"); // 迭代遍历 Set<String> keyset = user_map.keySet(); Iterator<String> it = keyset.iterator(); while (it.hasNext()) { System.out.println(user_map.get(it.next())); } System.out.println("------------------------------------------"); // map转entryset转list,转的真正含义是换个数据结构装数据 Set<Entry<String, Object>> set = user_map.entrySet(); Iterator<Entry<String, Object>> iteratory = set.iterator(); List<Object> list = new ArrayList<Object>(); while (iteratory.hasNext()) { Entry<String, Object> entry = iteratory.next(); list.add(entry.getKey()); list.add(entry.getValue()); } System.out.println(list); } }
test.User_Query_Test.java
package com.changping.jdbc.test; import java.util.List; import java.util.Map; import com.changping.jdbc.dao.UserDao; public class User_Query_Test { public static void main(String[] args) { UserDao userDao = new UserDao(); List<Map<String,Object>> user_list = userDao.queryUser(); System.out.println(user_list); } }
test.User_Update_Test.java
package com.changping.jdbc.test; import com.changping.jdbc.dao.UserDao; import com.changping.jdbc.pojo.User; public class User_Update_Test { public static void main(String [] args) { UserDao userDao = new UserDao(); User user = new User(); user.setDb_source("db0425"); user.setUserpass("789"); user.setUsername("tom"); int result = userDao.update_user_by_username(user); System.out.println(result); } }