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

最新推荐文章于 2022-08-02 16:37:27 发布

区分

最新推荐文章于 2022-08-02 16:37:27 发布

阅读量93

点赞数

文章标签： JAVA基础mysql和jdbc

本文链接：https://blog.csdn.net/weixin_32752827/article/details/113388523

版权

本文知识点(目录)：

1、JDBC连接MySQL数据库

1.1、我用到的jar包

1.2、实例演示

MySQL建表语句

1 --MySQL建表语句

2 create table user(3 id number(5) primary keyauto_increment,4 account varchar(20) not null,5 password varchar(32) not null

6 );

db.properties 配置文件

1 jdbcDriver=com.mysql.jdbc.Driver2 url=jdbc:mysql://localhost:3306/school3 userName=root4 password=123456

DBUtils 工具类(封装jdbc公共部分的代码)

1 packagecom.shore.util;2

3 importjava.io.IOException;4 importjava.io.InputStream;5 importjava.sql.Connection;6 importjava.sql.DriverManager;7 importjava.sql.PreparedStatement;8 importjava.sql.ResultSet;9 importjava.sql.SQLException;10 importjava.sql.Statement;11 importjava.util.Properties;12

13 /**

14 *@authorDSHORE/2020-1-1515 * 连接数据库--->工具类16 */

17 public classDBUtils {18

19 privateDBUtils() {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 = newProperties();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 newExceptionInInitializerError(e);47 }48 }49 }50

51 //连接数据库

52 public static Connection getConnection() throwsSQLException {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 returnconnection;60 }61

62 //释放资源。顺序：resultSet、statement、connection

63 public static voidfree(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 voidfree(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 packagecom.shore.entity;2

3 /**

4 *@authorDSHORE/2020-1-155 *6 */

7 public classUser {8 privateInteger id;9 privateString account;10 privateString password;11

12 publicInteger getId() {13 returnid;14 }15

16 public voidsetId(Integer id) {17 this.id =id;18 }19

20 publicString getAccount() {21 returnaccount;22 }23

24 public voidsetAccount(String account) {25 this.account =account;26 }27

28 publicString getPassword() {29 returnpassword;30 }31

32 public voidsetPassword(String password) {33 this.password =password;34 }35 }

dao层

1 //接口

2 public interfaceIUserDao {3 public int add(User user); //新增

4 public User findById(Integer id); //根据id查询

5 }

1 packagecom.shore.dao.impl;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.Statement;7

8 importcom.shore.dao.IUserDao;9 importcom.shore.entity.User;10 importcom.shore.util.DBUtils;11

12 /**

13 *@authorDSHORE/2020-1-1514 * 接口的实现类15 */

16 public class UserDao implementsIUserDao {17 //全局变量

18 Connection connection = null;19 Statement statement = null;20 PreparedStatement preparedStatement = null;21 ResultSet resultSet = null;22

23 @Override //新增

24 public intadd(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 returnresult;39 }40

41 @Override //根据id查询

42 publicUser 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 = newUser();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 returnuser;64 }65 }

UserTest 测试类

1 packagecom.shore.test;2

3 importorg.junit.Test;4

5 importcom.shore.dao.IUserDao;6 importcom.shore.dao.impl.UserDao;7 importcom.shore.entity.User;8

9 /**

10 *@authorDSHORE/2020-1-1511 *12 */

13 public classUserTest {14 @Test15 public voidaddTest() {16 IUserDao userDao = newUserDao();17 User user = newUser();18 user.setAccount("lisi");19 user.setPassword("123456");20 userDao.add(user); //添加成功

21 }22

23 @Test24 public voidfindByIdTest() {25 IUserDao userDao = newUserDao();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.OracleDriver2 url=jdbc:oracle:thin:@localhost:1521:shoreid3 userName=zhangsan4 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 packagecom.shore.util;2

13 /**

14 *@authorDSHORE/2020-1-1515 * 连接数据库--->工具类16 */

17 public classDBUtils {18

19 privateDBUtils() {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 = newProperties();29 try{30 //读取配置文件(Oracle数据库)

31 InputStream inputStream = DBUtils.class.getResourceAsStream("/odb.properties");32 properties.load(inputStream); //加载配置文件

33 inputStream.close();//关闭输入流34

35 //获取到对应参数的值

51 //连接数据库

58 }59 returnconnection;60 }61

62 //释放资源。顺序：resultSet、statement、connection

89 //释放资源。顺序：resultSet、preparedStatement、connection

User 实体类

1 packagecom.shore.entity;2

3 /**

4 *@authorDSHORE/2020-1-155 *6 */

7 public classUser {8 privateInteger id;9 privateString account;10 privateString password;11

12 publicInteger getId() {13 returnid;14 }15

16 public voidsetId(Integer id) {17 this.id =id;18 }19

20 publicString getAccount() {21 returnaccount;22 }23

24 public voidsetAccount(String account) {25 this.account =account;26 }27

28 publicString getPassword() {29 returnpassword;30 }31

32 public voidsetPassword(String password) {33 this.password =password;34 }35 }

dao层

1 //接口

2 public interfaceIUserDao {3 public int add(User user); //新增

4 public User findById(Integer id); //根据id查询

5 }

1 packagecom.shore.dao.impl;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.Statement;7

8 importcom.shore.dao.IUserDao;9 importcom.shore.entity.User;10 importcom.shore.util.DBUtils;11

12 /**

13 *@authorDSHORE/2020-1-1514 * 接口实现类15 */

16 public class UserDao implementsIUserDao {17 //全局变量

18 Connection connection = null;19 Statement statement = null;20 PreparedStatement preparedStatement = null;21 ResultSet resultSet = null;22

23 @Override //新增

24 public intadd(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 returnresult;39 }40

41 @Override //根据id查询

42 publicUser 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();//执行查询操作

UserTest 测试类

1 packagecom.shore.test;2

3 importorg.junit.Test;4

5 importcom.shore.dao.IUserDao;6 importcom.shore.dao.impl.UserDao;7 importcom.shore.entity.User;8

9 /**

10 *@authorDSHORE/2020-1-1511 *12 */

21 }22

23 @Test24 public voidfindByIdTest() {25 IUserDao userDao = newUserDao();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 keyauto_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

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; --不建缓冲区。

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.OracleDriver2 url=jdbc:oracle:thin:@localhost:1521:shoreid3 userName=zhangsan4 password=123456

DBUtils 工具类

1 packagecom.shore.util;2

13 /**

14 *@authorDSHORE/2020-1-1515 * 连接数据库--->工具类16 */

17 public classDBUtils {18

19 privateDBUtils() {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 = newProperties();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 newExceptionInInitializerError(e);50 }51 }52 }53

54 //连接数据库

55 public static Connection getConnection() throwsSQLException {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 returnconnection;63 }64

65 //释放资源。顺序：resultSet、statement、connection

66 public static voidfree(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 voidfree(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 packagecom.shore.entity;2

3 /**

4 *@authorDSHORE/2020-1-155 *6 */

7 public classUser {8 privateInteger id;9 privateString account;10 privateString password;11

12 publicInteger getId() {13 returnid;14 }15

16 public voidsetId(Integer id) {17 this.id =id;18 }19

20 publicString getAccount() {21 returnaccount;22 }23

24 public voidsetAccount(String account) {25 this.account =account;26 }27

28 publicString getPassword() {29 returnpassword;30 }31

32 public voidsetPassword(String password) {33 this.password =password;34 }35 }

DAO层

1 packagecom.shore.dao;2

3 importcom.shore.entity.User;4

5 /**

6 *@authorDSHORE/2020-1-157 * 接口8 */

9 public interfaceIUserDao {10 public int add(User user); //新增

11 public User findById(Integer id); //根据id查询

12 }

1 packagecom.shore.dao.impl;2

3 importjava.sql.Connection;4 importjava.sql.PreparedStatement;5 importjava.sql.ResultSet;6 importjava.sql.Statement;7

8 importcom.shore.dao.IUserDao;9 importcom.shore.entity.User;10 importcom.shore.util.DBUtils;11

12 /**

13 *@authorDSHORE/2020-1-1514 *15 */

16 public class UserDao implementsIUserDao {17 //全局变量

18 Connection connection = null;19 Statement statement = null;20 PreparedStatement preparedStatement = null;21 ResultSet resultSet = null;22

23 @Override //新增

24 public intadd(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 returnresult;43 }44

45 @Override //根据id查询

46 publicUser 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 */

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 = newUser();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 returnuser;74 }75 }

UserTest 测试类

1 packagecom.shore.test;2

3 importorg.junit.Test;4

5 importcom.shore.dao.IUserDao;6 importcom.shore.dao.impl.UserDao;7 importcom.shore.entity.User;8

9 /**

10 *@authorDSHORE/2020-1-1511 *12 */

21 }22

23 @Test24 public voidfindByIdTest() {25 IUserDao userDao = newUserDao();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 的测试结果图

均测试成功

区分

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
JAVA基础mysql和jdbc_Java基础102 完整的JDBC技术连接MySQL数据库和Oracle数据库的方法实例...

本文知识点(目录)：1、JDBC连接MySQL数据库1.1、我用到的jar包1.2、实例演示MySQL建表语句1 --MySQL建表语句2 create table user(3 id number(5) primary keyauto_increment,4 account varchar(20) not null,5 password varchar(32) not null6 )...
复制链接

扫一扫