Java基础102 完整的JDBC技术连接MySQL数据库和Oracle数据库的方法实例

本文知识点（目录）：

    1、jdbc连接MySQL数据库
    2、jdbc连接Oracle数据库
    3、附录【本文jdbc连接MySQL或Oracle数据库的全部代码（合起来，做对比）】

1、JDBC连接MySQL数据库

1.1、我用到的jar包

1.2、实例演示

MySQL建表语句

1 -- MySQL建表语句
2 create table user(
3   id number(5) primary key auto_increment,
4   account varchar(20) not null,
5   password varchar(32) not null
6 );

db.properties 配置文件

1 jdbcDriver=com.mysql.jdbc.Driver
2 url=jdbc:mysql://localhost:3306/school
3 userName=root
4 password=123456

DBUtils 工具类（封装jdbc公共部分的代码）

  1 package com.shore.util;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.sql.Connection;
  6 import java.sql.DriverManager;
  7 import java.sql.PreparedStatement;
  8 import java.sql.ResultSet;
  9 import java.sql.SQLException;
 10 import java.sql.Statement;
 11 import java.util.Properties;
 12 
 13 /**
 14  * @author DSHORE/2020-1-15
 15  * 连接数据库--->工具类
 16  */
 17 public class DBUtils {
 18     
 19     private DBUtils() {
 20     }
 21 
 22     private static String jdbcDriver = "";
 23     private static String url = "";
 24     private static String userName = "";
 25     private static String password = "";
 26 
 27     static {
 28         Properties properties = new Properties();
 29         try {
 30             //读取配置文件（MySQL数据库）
 31             InputStream inputStream = DBUtils.class.getResourceAsStream("/db.properties");
 32             properties.load(inputStream); //加载配置文件
 33             inputStream.close();//关闭输入流
 34             
 35             //获取到对应参数的值
 36             jdbcDriver = properties.getProperty("jdbcDriver");
 37             url = properties.getProperty("url");
 38             userName = properties.getProperty("userName");
 39             password = properties.getProperty("password");
 40         } catch (IOException e1) {
 41             e1.printStackTrace();
 42         } finally {
 43             try {
 44                 Class.forName(jdbcDriver);
 45             } catch (ClassNotFoundException e) {
 46                 throw new ExceptionInInitializerError(e);
 47             }
 48         }
 49     }
 50 
 51     //连接数据库
 52     public static Connection getConnection() throws SQLException {
 53         Connection connection = null;
 54         try {
 55             connection = DriverManager.getConnection(url, userName, password);
 56         } catch (Exception e) {
 57             System.out.println(e.getMessage());//如果出现异常，则 把异常信息打印到控台上
 58         }
 59         return connection;
 60     }
 61 
 62     // 释放资源。   顺序：resultSet、statement、connection
 63     public static void free(ResultSet resultSet, Statement statement, Connection connection) {
 64         if (resultSet != null) {
 65             try {
 66                 resultSet.close();
 67             } catch (SQLException e) {
 68                 e.printStackTrace();
 69             } finally {
 70                 if (statement != null) {
 71                     try {
 72                         statement.close();
 73                     } catch (SQLException e) {
 74                         e.printStackTrace();
 75                     } finally {
 76                         if (connection != null) {
 77                             try {
 78                                 connection.close();
 79                             } catch (SQLException e) {
 80                                 e.printStackTrace();
 81                             }
 82                         }
 83                     }
 84                 }
 85             }
 86         }
 87     }
 88 
 89     // 释放资源。  顺序：resultSet、preparedStatement、connection
 90     public static void free(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
 91         if (resultSet != null) {
 92             try {
 93                 resultSet.close();
 94             } catch (SQLException e) {
 95                 e.printStackTrace();
 96             } finally {
 97                 if (preparedStatement != null) {
 98                     try {
 99                         preparedStatement.close();
100                     } catch (SQLException e) {
101                         e.printStackTrace();
102                     } finally {
103                         if (connection != null) {
104                             try {
105                                 connection.close();
106                             } catch (SQLException e) {
107                                 e.printStackTrace();
108                             }
109                         }
110                     }
111                 }
112             }
113         }
114     }
115 }

User 实体类

 1 package com.shore.entity;
 2 
 3 /**
 4  * @author DSHORE/2020-1-15
 5  * 
 6  */
 7 public class User {
 8     private Integer id;
 9     private String account;
10     private String password;
11 
12     public Integer getId() {
13         return id;
14     }
15 
16     public void setId(Integer id) {
17         this.id = id;
18     }
19 
20     public String getAccount() {
21         return account;
22     }
23 
24     public void setAccount(String account) {
25         this.account = account;
26     }
27 
28     public String getPassword() {
29         return password;
30     }
31 
32     public void setPassword(String password) {
33         this.password = password;
34     }
35 }

dao层

1 //接口
2 public interface IUserDao {
3     public int add(User user); //新增
4     public User findById(Integer id); //根据id查询
5 }

 1 package com.shore.dao.impl;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import com.shore.dao.IUserDao;
 9 import com.shore.entity.User;
10 import com.shore.util.DBUtils;
11 
12 /**
13  * @author DSHORE/2020-1-15
14  *  接口的实现类
15  */
16 public class UserDao implements IUserDao {
17     //全局变量
18     Connection connection = null;
19     Statement statement = null;
20     PreparedStatement preparedStatement = null;
21     ResultSet resultSet = null;
22 
23     @Override //新增
24     public int add(User user) {
25         int result = 0;
26         String sql = "";
27         try {
28             connection = DBUtils.getConnection();
29             //MySQL数据库
30             sql = "insert into user(account,password) values('"+user.getAccount()+"','"+user.getPassword()+"')";
31             statement = connection.createStatement();
32             result = statement.executeUpdate(sql);//执行添加操作
33         } catch (Exception e) {
34             e.printStackTrace();
35         } finally{
36             DBUtils.free(null, statement, connection);
37         }
38         return result;
39     }
40 
41     @Override //根据id查询
42     public User findById(Integer id) {
43         User user = null;    
44         String sql = "";
45         try {
46             connection = DBUtils.getConnection();//连接数据库
47             //MySQL
48             sql = "select * from user where id = ?";
49             preparedStatement = connection.prepareStatement(sql);//编译/检查sql语句是否符合规范等等
50             preparedStatement.setInt(1, id);
51             resultSet = preparedStatement.executeQuery();//执行查询操作
52             if(resultSet.first()){
53                 user = new User();
54                 user.setId(id);
55                 user.setAccount(resultSet.getString("account"));
56                 user.setPassword(resultSet.getString("password"));
57             }
58         } catch (Exception e) {
59             e.fillInStackTrace();
60         } finally {
61             DBUtils.free(resultSet, preparedStatement, connection);
62         }
63         return user;
64     }
65 }

UserTest 测试类

 1 package com.shore.test;
 2 
 3 import org.junit.Test;
 4 
 5 import com.shore.dao.IUserDao;
 6 import com.shore.dao.impl.UserDao;
 7 import com.shore.entity.User;
 8 
 9 /**
10  * @author DSHORE/2020-1-15
11  *
12  */
13 public class UserTest {
14     @Test
15     public void addTest() {
16         IUserDao userDao = new UserDao();
17         User user = new User();
18         user.setAccount("lisi");
19         user.setPassword("123456");
20         userDao.add(user); //添加成功
21     }
22     
23     @Test
24     public void findByIdTest() {
25         IUserDao userDao = new UserDao();
26         User user = userDao.findById(2);
27         System.out.println(user); //返回值： com.shore.entity.User@1c5d9084   
28         System.out.println(user.getAccount()+" : "+user.getPassword()); //返回值： 张三 : 123456  
29     }
30 }

2、JDBC连接Oracle数据库

2.1、我用到的jar包

2.2、实例演示

Oracle 建表语句

 1 -- Oracle建表语句
 2 create table users( -- user是Oracle关键字，不能用user
 3   id number(5) primary key,
 4   account varchar2(20) not null unique,
 5   password varchar2(32) not null
 6 );
 7 
 8 -- 创建序列
 9 create sequence users_seq  -- 序列名称：users_seq
10 minvalue 1  --最小值
11 start with 1   --从1开始计数，数值可变
12 increment by 1  --每次加1，数值可变
13 nocycle  --一直累加，不循环；cycle：达到最大值后，将从头开始累加。 （maxvalue 999 -- 最大值）
14 nocache;  --不建缓冲区。

这里我没有创建触发器，dao层插入数据时，id这项必须要写上，比如：看红色代码处

insert into users(id,account,password) values(users_seq.nextval,'张三','123456');

创建触发器（主要看Oracle部分，第3小点是创建触发器的步骤，第4小点体现了触发器的作用）详情：https://www.cnblogs.com/dshore123/p/8267240.html

odb.properties 配置文件

1 jdbcDriver=oracle.jdbc.driver.OracleDriver
2 url=jdbc:oracle:thin:@localhost:1521:shoreid
3 userName=zhangsan
4 password=123456

注意：数据库名是shore，该数据库的SID是shoreid。

其中，命令行窗口和Oracle的PLSQL Developer默认是以全局数据库名的形式来登录，即：sqlplus zhangsan/123456@shore
而Java代码连接时，是以该全局数据库的SID的形式来登录，即：url=jdbc:oracle:thin:@localhost:1521:shoreid

这要看你在创建数据库时的设置，全局数据库名和SID可以设置一样。

DBUtils 工具类（封装jdbc公共部分的代码）

  1 package com.shore.util;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.sql.Connection;
  6 import java.sql.DriverManager;
  7 import java.sql.PreparedStatement;
  8 import java.sql.ResultSet;
  9 import java.sql.SQLException;
 10 import java.sql.Statement;
 11 import java.util.Properties;
 12 
 13 /**
 14  * @author DSHORE/2020-1-15
 15  * 连接数据库--->工具类
 16  */
 17 public class DBUtils {
 18     
 19     private DBUtils() {
 20     }
 21 
 22     private static String jdbcDriver = "";
 23     private static String url = "";
 24     private static String userName = "";
 25     private static String password = "";
 26 
 27     static {
 28         Properties properties = new Properties();
 29         try {
 30             //读取配置文件（Oracle数据库）
 31             InputStream inputStream = DBUtils.class.getResourceAsStream("/odb.properties");
 32             properties.load(inputStream); //加载配置文件
 33             inputStream.close();//关闭输入流
 34             
 35             //获取到对应参数的值
 36             jdbcDriver = properties.getProperty("jdbcDriver");
 37             url = properties.getProperty("url");
 38             userName = properties.getProperty("userName");
 39             password = properties.getProperty("password");
 40         } catch (IOException e1) {
 41             e1.printStackTrace();
 42         } finally {
 43             try {
 44                 Class.forName(jdbcDriver);
 45             } catch (ClassNotFoundException e) {
 46                 throw new ExceptionInInitializerError(e);
 47             }
 48         }
 49     }
 50 
 51     //连接数据库
 52     public static Connection getConnection() throws SQLException {
 53         Connection connection = null;
 54         try {
 55             connection = DriverManager.getConnection(url, userName, password);
 56         } catch (Exception e) {
 57             System.out.println(e.getMessage());//如果出现异常，则 把异常信息打印到控台上
 58         }
 59         return connection;
 60     }
 61 
 62     // 释放资源。   顺序：resultSet、statement、connection
 63     public static void free(ResultSet resultSet, Statement statement, Connection connection) {
 64         if (resultSet != null) {
 65             try {
 66                 resultSet.close();
 67             } catch (SQLException e) {
 68                 e.printStackTrace();
 69             } finally {
 70                 if (statement != null) {
 71                     try {
 72                         statement.close();
 73                     } catch (SQLException e) {
 74                         e.printStackTrace();
 75                     } finally {
 76                         if (connection != null) {
 77                             try {
 78                                 connection.close();
 79                             } catch (SQLException e) {
 80                                 e.printStackTrace();
 81                             }
 82                         }
 83                     }
 84                 }
 85             }
 86         }
 87     }
 88 
 89     // 释放资源。  顺序：resultSet、preparedStatement、connection
 90     public static void free(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
 91         if (resultSet != null) {
 92             try {
 93                 resultSet.close();
 94             } catch (SQLException e) {
 95                 e.printStackTrace();
 96             } finally {
 97                 if (preparedStatement != null) {
 98                     try {
 99                         preparedStatement.close();
100                     } catch (SQLException e) {
101                         e.printStackTrace();
102                     } finally {
103                         if (connection != null) {
104                             try {
105                                 connection.close();
106                             } catch (SQLException e) {
107                                 e.printStackTrace();
108                             }
109                         }
110                     }
111                 }
112             }
113         }
114     }
115 }

User 实体类

 1 package com.shore.entity;
 2 
 3 /**
 4  * @author DSHORE/2020-1-15
 5  * 
 6  */
 7 public class User {
 8     private Integer id;
 9     private String account;
10     private String password;
11 
12     public Integer getId() {
13         return id;
14     }
15 
16     public void setId(Integer id) {
17         this.id = id;
18     }
19 
20     public String getAccount() {
21         return account;
22     }
23 
24     public void setAccount(String account) {
25         this.account = account;
26     }
27 
28     public String getPassword() {
29         return password;
30     }
31 
32     public void setPassword(String password) {
33         this.password = password;
34     }
35 }

dao层

1 //接口
2 public interface IUserDao {
3     public int add(User user); //新增
4     public User findById(Integer id); //根据id查询
5 }

 1 package com.shore.dao.impl;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import com.shore.dao.IUserDao;
 9 import com.shore.entity.User;
10 import com.shore.util.DBUtils;
11 
12 /**
13  * @author DSHORE/2020-1-15
14  *  接口实现类
15  */
16 public class UserDao implements IUserDao {
17     //全局变量
18     Connection connection = null;
19     Statement statement = null;
20     PreparedStatement preparedStatement = null;
21     ResultSet resultSet = null;
22 
23     @Override //新增
24     public int add(User user) {
25         int result = 0;
26         String sql = "";
27         try {
28             connection = DBUtils.getConnection();
29             //Oracle数据库
30             sql = "insert into users values(users_seq.nextval,'"+user.getAccount()+"','"+user.getPassword()+"')";            
31             statement = connection.createStatement();
32             result = statement.executeUpdate(sql);//执行添加操作
33         } catch (Exception e) {
34             e.printStackTrace();
35         } finally{
36             DBUtils.free(null, statement, connection);
37         }
38         return result;
39     }
40 
41     @Override //根据id查询
42     public User findById(Integer id) {
43         User user = null;    
44         String sql = "";
45         try {
46             connection = DBUtils.getConnection();//连接数据库
47             //Oracle
48             sql = "select * from users where id = ?";
49             preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
50             preparedStatement.setInt(1, id);
51             resultSet = preparedStatement.executeQuery();//执行查询操作
52             if(resultSet.first()){
53                 user = new User();
54                 user.setId(id);
55                 user.setAccount(resultSet.getString("account"));
56                 user.setPassword(resultSet.getString("password"));
57             }
58         } catch (Exception e) {
59             e.fillInStackTrace();
60         } finally {
61             DBUtils.free(resultSet, preparedStatement, connection);
62         }
63         return user;
64     }
65 }

UserTest 测试类

 1 package com.shore.test;
 2 
 3 import org.junit.Test;
 4 
 5 import com.shore.dao.IUserDao;
 6 import com.shore.dao.impl.UserDao;
 7 import com.shore.entity.User;
 8 
 9 /**
10  * @author DSHORE/2020-1-15
11  *
12  */
13 public class UserTest {
14     @Test
15     public void addTest() {
16         IUserDao userDao = new UserDao();
17         User user = new User();
18         user.setAccount("lisi");
19         user.setPassword("123456");
20         userDao.add(user); //添加成功
21     }
22     
23     @Test
24     public void findByIdTest() {
25         IUserDao userDao = new UserDao();
26         User user = userDao.findById(2);
27         System.out.println(user); //返回值： com.shore.entity.User@42197bb7
28         System.out.println(user.getAccount()+" : "+user.getPassword()); //返回值： lisi : 123456
29     }
30 }

到此已完结（均测试成功）

附录

本文jdbc连接MySQL或Oracle数据库的全部代码（合起来，做对比）

用到的jar包

测试项目截图

其中db.properties 和 odb.properties 配置文件是在src目录下，与com包名是同一级。

建表语句（SQL.sql）

 1 -- MySQL建表语句
 2 create table user(
 3   id number(5) primary key auto_increment,
 4   account varchar(20) not null,
 5   password varchar(32) not null
 6 );
 7 -- 插入数据（这里不是手动插入，而是使用测试类UserTest来插入）
 8 insert into user(account,password) values("张三","123456");
 9 insert into user(account,password) values("lisi","123456");
10 
11 
12 -- Oracle建表语句
13 create table users( -- user是Oracle关键字，不能用user
14   id number(5) primary key,
15   account varchar2(20) not null unique,
16   password varchar2(32) not null
17 );
18 
19 -- 创建序列
20 create sequence users_seq  -- 序列名称：users_seq
21 minvalue 1  --最小值（如果出现这个错误  ORA-00001:违反唯一约束条件；那么next number 就设置大一些，设置成10足够了）
22 start with 1   --从1开始计数，数值可变
23 increment by 1  --每次加1，数值可变
24 nocycle  --一直累加，不循环；cycle：达到最大值后，将从头开始累加。  （maxvalue 999 -- 最大值）
25 nocache;  --不建缓冲区。
26 
27 -- 插入数据（这里不是手动插入，而是使用测试类UserTest来插入）
28 insert into users(id,account,password) values(users_seq.nextval,'张三','123456');
29 insert into users(id,account,password) values(users_seq.nextval,'lisi','123456');

db.properties 配置文件

jdbcDriver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/school
userName=root
password=123456

odb.properties 配置文件

1 jdbcDriver=oracle.jdbc.driver.OracleDriver
2 url=jdbc:oracle:thin:@localhost:1521:shoreid
3 userName=zhangsan
4 password=123456

DBUtils 工具类

  1 package com.shore.util;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.sql.Connection;
  6 import java.sql.DriverManager;
  7 import java.sql.PreparedStatement;
  8 import java.sql.ResultSet;
  9 import java.sql.SQLException;
 10 import java.sql.Statement;
 11 import java.util.Properties;
 12 
 13 /**
 14  * @author DSHORE/2020-1-15
 15  * 连接数据库--->工具类
 16  */
 17 public class DBUtils {
 18     
 19     private DBUtils() {
 20     }
 21 
 22     private static String jdbcDriver = "";
 23     private static String url = "";
 24     private static String userName = "";
 25     private static String password = "";
 26 
 27     static {
 28         Properties properties = new Properties();
 29         try {
 30             //读取配置文件（MySQL数据库）
 31             //InputStream inputStream = DBUtils.class.getResourceAsStream("/db.properties");
 32             
 33             //读取配置文件（Oracle数据库）
 34             InputStream inputStream = DBUtils.class.getResourceAsStream("/odb.properties");
 35             properties.load(inputStream); //加载配置文件
 36             inputStream.close();//关闭输入流
 37             
 38             //获取到对应参数的值
 39             jdbcDriver = properties.getProperty("jdbcDriver");
 40             url = properties.getProperty("url");
 41             userName = properties.getProperty("userName");
 42             password = properties.getProperty("password");
 43         } catch (IOException e1) {
 44             e1.printStackTrace();
 45         } finally {
 46             try {
 47                 Class.forName(jdbcDriver);
 48             } catch (ClassNotFoundException e) {
 49                 throw new ExceptionInInitializerError(e);
 50             }
 51         }
 52     }
 53 
 54     //连接数据库
 55     public static Connection getConnection() throws SQLException {
 56         Connection connection = null;
 57         try {
 58             connection = DriverManager.getConnection(url, userName, password);
 59         } catch (Exception e) {
 60             System.out.println(e.getMessage());//如果出现异常，则 把异常信息打印到控台上
 61         }
 62         return connection;
 63     }
 64 
 65     // 释放资源。   顺序：resultSet、statement、connection
 66     public static void free(ResultSet resultSet, Statement statement, Connection connection) {
 67         if (resultSet != null) {
 68             try {
 69                 resultSet.close();
 70             } catch (SQLException e) {
 71                 e.printStackTrace();
 72             } finally {
 73                 if (statement != null) {
 74                     try {
 75                         statement.close();
 76                     } catch (SQLException e) {
 77                         e.printStackTrace();
 78                     } finally {
 79                         if (connection != null) {
 80                             try {
 81                                 connection.close();
 82                             } catch (SQLException e) {
 83                                 e.printStackTrace();
 84                             }
 85                         }
 86                     }
 87                 }
 88             }
 89         }
 90     }
 91 
 92     // 释放资源。  顺序：resultSet、preparedStatement、connection
 93     public static void free(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
 94         if (resultSet != null) {
 95             try {
 96                 resultSet.close();
 97             } catch (SQLException e) {
 98                 e.printStackTrace();
 99             } finally {
100                 if (preparedStatement != null) {
101                     try {
102                         preparedStatement.close();
103                     } catch (SQLException e) {
104                         e.printStackTrace();
105                     } finally {
106                         if (connection != null) {
107                             try {
108                                 connection.close();
109                             } catch (SQLException e) {
110                                 e.printStackTrace();
111                             }
112                         }
113                     }
114                 }
115             }
116         }
117     }
118 }

User 实体类

 1 package com.shore.entity;
 2 
 3 /**
 4  * @author DSHORE/2020-1-15
 5  * 
 6  */
 7 public class User {
 8     private Integer id;
 9     private String account;
10     private String password;
11 
12     public Integer getId() {
13         return id;
14     }
15 
16     public void setId(Integer id) {
17         this.id = id;
18     }
19 
20     public String getAccount() {
21         return account;
22     }
23 
24     public void setAccount(String account) {
25         this.account = account;
26     }
27 
28     public String getPassword() {
29         return password;
30     }
31 
32     public void setPassword(String password) {
33         this.password = password;
34     }
35 }

DAO层

 1 package com.shore.dao;
 2 
 3 import com.shore.entity.User;
 4 
 5 /**
 6  * @author DSHORE/2020-1-15
 7  * 接口
 8  */
 9 public interface IUserDao {
10     public int add(User user); //新增
11     public User findById(Integer id); //根据id查询
12 }

 1 package com.shore.dao.impl;
 2 
 3 import java.sql.Connection;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.Statement;
 7 
 8 import com.shore.dao.IUserDao;
 9 import com.shore.entity.User;
10 import com.shore.util.DBUtils;
11 
12 /**
13  * @author DSHORE/2020-1-15
14  *
15  */
16 public class UserDao implements IUserDao {
17     //全局变量
18     Connection connection = null;
19     Statement statement = null;
20     PreparedStatement preparedStatement = null;
21     ResultSet resultSet = null;
22 
23     @Override //新增
24     public int add(User user) {
25         int result = 0;
26         String sql = "";
27         try {
28             connection = DBUtils.getConnection();
29             //MySQL语句
30             //sql = "insert into user(account,password) values('"+user.getAccount()+"','"+user.getPassword()+"')";
31             
32             //Oracle语句
33             sql = "insert into users values(users_seq.nextval,'"+user.getAccount()+"','"+user.getPassword()+"')";
34             
35             statement = connection.createStatement();
36             result = statement.executeUpdate(sql);//执行添加操作
37         } catch (Exception e) {
38             e.printStackTrace();
39         } finally{
40             DBUtils.free(null, statement, connection);
41         }
42         return result;
43     }
44 
45     @Override //根据id查询
46     public User findById(Integer id) {
47         User user = null;    
48         String sql = "";
49         try {
50             connection = DBUtils.getConnection();//连接数据库
51             //MySQL
52             /*sql = "select * from user where id = ?";
53             preparedStatement = connection.prepareStatement(sql);//编译/检查sql语句是否符合规范等等
54             */
55             
56             //Oracle
57             sql = "select * from users where id = ?";
58             preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
59             
60             preparedStatement.setInt(1, id);
61             resultSet = preparedStatement.executeQuery();//执行查询操作
62             if(resultSet.first()){
63                 user = new User();
64                 user.setId(id);
65                 user.setAccount(resultSet.getString("account"));
66                 user.setPassword(resultSet.getString("password"));
67             }
68         } catch (Exception e) {
69             e.fillInStackTrace();
70         } finally {
71             DBUtils.free(resultSet, preparedStatement, connection);
72         }
73         return user;
74     }
75 }

UserTest 测试类

 1 package com.shore.test;
 2 
 3 import org.junit.Test;
 4 
 5 import com.shore.dao.IUserDao;
 6 import com.shore.dao.impl.UserDao;
 7 import com.shore.entity.User;
 8 
 9 /**
10  * @author DSHORE/2020-1-15
11  *
12  */
13 public class UserTest {
14     @Test
15     public void addTest() {
16         IUserDao userDao = new UserDao();
17         User user = new User();
18         user.setAccount("lisi");
19         user.setPassword("123456");
20         userDao.add(user); //添加成功
21     }
22     
23     @Test
24     public void findByIdTest() {
25         IUserDao userDao = new UserDao();
26         User user = userDao.findById(2);
27         System.out.println(user); //返回值：【MySQL】com.shore.entity.User@1c5d9084      【Oracle】com.shore.entity.User@42197bb7
28         System.out.println(user.getAccount()+" : "+user.getPassword()); //返回值：【MySQL】张三 : 123456     【Oracle】lisi : 123456
29     }
30 }

Oracle 的测试结果图

均测试成功

原创作者：DSHORE

作者主页：http://www.cnblogs.com/dshore123/

原文出自：https://www.cnblogs.com/dshore123/p/12198975.html

posted @ 2020-01-15 21:56 DSHORE 阅读(638) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

DSHORE

攻城狮在东莞相亲，成功后即将上线！

Java基础102 完整的JDBC技术连接MySQL数据库和Oracle数据库的方法实例

1、JDBC连接MySQL数据库

2、JDBC连接Oracle数据库

附录

公告