基于Oracle的jdbc增删查改
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了基于Oracle的jdbc增删查改，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10581字，纯文字阅读大概需要16分钟。
内容图文
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);
}
}
内容总结
以上是互联网集市为您收集整理的基于Oracle的jdbc增删查改全部内容，希望文章能够帮你解决基于Oracle的jdbc增删查改所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】