一、JDBC简介
1、Java Data BaseConnectivity(Java数据库连接):是java与数据库的桥梁,提供读写操作
2、JDBC:用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问。
通过JDBC可以连接Oracle、MySql、SqlServer数据库
3、JDBC:应用服务器和数据库DB之间的数据流通过程,实现应用服务器对数据库的操作。
二、JDBC使用
1.加载驱动程序:
Class.forName(driverClass)
Class.forName("com.mysql.jdbc.Driver");
加载Oracle驱动:(需要导入ojdc6.jar的jar包)
Class.forName("oracle.jdbc.OracleDriver");
***注意:驱动是固定写法
常用驱动类
(1)Oracle的Driver oracle.jdbc.driver.OracleDriver(2)mysql的Driver com.mysql.jdbc.Driver
(3)SQLServer的Driver的全名 com.microsoft.jdbc.sqlserver.SQLServerDriver2:
oracle:
plsql右键属性按安装路径(如:D:\app\product\11.2.0\dbhome_2\jdbc\lib)找到jdbc\lib目录下可找到odbc包。
myeclipse java项目导包:
项目右键——>Build Path——>Configure BuilPath
左边选择Java Build Path 上边选择Libraries 右边选择Add External JARs
导入Web项目时可能出现的问题 http://blog.csdn.net/qq_29028175/article/details/54708367
2.连接到数据库:
DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/imooc","root","root");
或Oracle URL
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orl";
String user = "scott";
String password = "tiger";
//2.connect to the database
conn = DriverManager.getConnection(url, user, password);
***注释:其中jdbc:mysql表示jdbc连接mysql,127.0.0.1:3306为服务器地址和端口,imooc为数据库名称,root分别是用户名和密码
常用的url书写格式
(1)Oracle URL的格式 例:jdbc:oracle:thin:@192.168.0.20:1521:tarenadb(2)MySql URL的写法 例: jdbc:mysql://localhost:3306/tarena
(3)SQLServer URL的写法 例:jdbc:microsoft:sqlserver://localhost:1433/test
3.执行SQL语句:
stmt = conn.createStatement();
String sql="Select * from emp";
rs = stmt.executeQuery(sql);
或executeUpdate()方法
String sql = "insert into sporter values(1008,'丁','男','计算机系')";
stmt.executeUpdate(sql);
Orat的方法作用和区别
excute():任何语句都可以executeUpdate():增,删,改,创建
executeQuery():查询
或使用预编译语句PreparedStatement,?为占位符
String sql ="update tb_resume_basicinfo set head_shot=? where basicinfo_id=?";
PreparedStatement pstmt=conn.prepareStatement(sql);
pstmt.setString(1, newFileName);
pstmt.setInt(2, basicinfoID);
pstmt.executeUpdate();
4.循环取出结果:
while (rs.next())
5.转换为JAVA中的类型:
System.out.println(rs.getString(1) + " "+ rs.getString("ename")+" "+rs.getString(3));
6.关闭,释放
finally {
//有异常发生也会确保释放资源
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
三、实践:
1.连接MySQL数据库
建立数据库,并用以下语句建表
use imooc;
CREATE TABLE `imooc_goddess` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(30) NOT NULL,
`sex` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`birthday` date DEFAULT NULL,
`email` varchar(30) DEFAULT NULL,
`mobile` varchar(11) DEFAULT NULL,
`create_user` varchar(30) DEFAULT NULL,
`create_date` date DEFAULT NULL,
`update_user` varchar(30) DEFAULT NULL,
`update_date` date DEFAULT NULL,
`isdel` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Java代码连接数据库,并与对数库进行查询。[这种写法不规范,存在许多问题]
package com.imooc.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static final String url="jdbc:mysql://127.0.0.1:3306/imooc";
private static final String user="root";
private static final String password="1996";
public static void main(String[] args) throws ClassNotFoundException, SQLException
{
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库连接
Connection conn=DriverManager.getConnection(url, user, password);
//通过数据库的连接操纵数据库,实现增删改查。
Statement stmt=conn.createStatement();
ResultSet rs=stmt.executeQuery("select user_name,age from imooc_goddess");
while(rs.next())
{
System.out.print(rs.getString("user_name")+","+rs.getString("age"));
}
}
}
查询结果与数据库一致
小美,22
2.连接Oracle数据库
[推荐使用这种规范的写法]
package com.oracle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
// 1.load driver
Class.forName("oracle.jdbc.OracleDriver");
// new oracle.jdbc.OracleDriver();
// 2.connect to the database
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orl";
String user = "scott";
String password = "tiger";
conn = DriverManager.getConnection(url, user, password);
// 3.excute the sql
stmt = conn.createStatement();
String sql = "select * from emp";
rs = stmt.executeQuery(sql);
// 4.retrieve the result data
while (rs.next()) {
// 5.show the result data
System.out.println(rs.getInt(1) + " " + rs.getString("ename")
+ " " + rs.getString(3));
}
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 6.close
finally {
//有异常发生也会确保释放资源
try {
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
查询结果
3.使用配置文件
mysql.properties
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/qst
user=root
password=mysql
Config.java
package com.java;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
//配置类
public class Config {
private static Properties p = null;
static {
p = new Properties();
try {
p.load(new FileInputStream("config\\mysql.properties"));
} catch (IOException e) {
e.printStackTrace();
}
}
//获取对应键的值
public static String getValue(String key){
return p.getProperty(key);
}
}
DBUtil.java
package com.java;
import java.sql.*;
public class DBUtil {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
/**
* 获得数据库连接
*/
public Connection getConnection() {
//通过Config获取数据库配置信息
String driver = Config.getValue("driver");
String url = Config.getValue("url");
String user = Config.getValue("user");
String password = Config.getValue("password");
try {
//加载驱动程序
Class.forName(driver);
//建立数据库连接
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
/**
* 释放资源
*/
public void closeAll() {
//如果rs不为空,关闭rs
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* 执行sql语进行查询
*/
public ResultSet executeQuery(String preparedSql, String[] param) {
//处理sql,执行sql
try {
//得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if (param != null) {
for (int i = 0; i < param.length; i++) {
//为预编译sql设置参数
pstmt.setString(i + 1, param[i]);
}
}
//执行sql语句
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
/**
* 执行sql语句,可以进行增删改的操作,不能执行查询
*/
public int executeUpdate(String preparedSql, String[] param) {
int num = 0;
//处理sql、执行sql
try {
//得到PreparedStatement对象
pstmt = conn.prepareStatement(preparedSql);
if(param!=null) {
for (int i = 0; i < param.length; i++) {
//为预编译sql设置参数
pstmt.setString(i+1,param[i]);
}
}
//执行sql语句
num = pstmt.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}
return num;
}
}
DBUtilTest.java
package com.java;
import org.junit.*;
import java.sql.ResultSet;
import java.sql.SQLException;
import static org.junit.Assert.*;
public class DBUtilTest {
DBUtil db = new DBUtil();
@Before
public void getConnection() {
db.getConnection();
}
@Test
public void executeQuery(){
String selectSql ="select * from userdetails";
ResultSet rs = db.executeQuery(selectSql,null);
try {
while (rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2)
+" "+rs.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
@Test
public void executeUpadate() {
String updateSql = "";
String deleteSql = "";
String insertSql = "insert into userdetails(id,username,password,sex) values(?,?,?,?)";
int count = db.executeUpdate(insertSql, new String[]{"1001", "qdgxq", "mima", "1"});
System.out.println(count);
}
@After
public void closeAll() {
db.closeAll();
}
}
MyEclipse DB Browser(MyEclipse 数据库视图)
Windows->Show View->Others->MyEclipse Databse->DB Browser 右键new connection
3.jdbc处理存储过程
--(6) 创建一个名为‘检索信息’的存储过程,该存储过程完成检索指定员工号所参与的项目总数,其中员工号为输入参数,项目总数为输出参数
create or replace procedure 检索信息 (tsno in number,a out number,b out number)
as
begin
select sno,count(*)
into a,b
from sp
where sno=tsno
group by sno;
end 检索信息;
--(7) 调用‘检索信息’存储过程,完成检索1号员工参与的项目总数。
variable a number;
variable b number;
exec 检索信息(1,:a,:b);
print a b;
输出A 1 B 2
java语句
package com.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.concurrent.Callable;
public class TestProc {
public static void main(String[] args) {
Connection conn = null;
CallableStatement cstmt = null; //用于调用存储过程
try {
// 1.load driver
Class.forName("oracle.jdbc.OracleDriver");
// new oracle.jdbc.OracleDriver();
// 2.connect to the database
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orl";
String user = "excise";
String password = "123";
conn = DriverManager.getConnection(url, user, password);
// 3.excute the sql
cstmt = conn.prepareCall("{call 检索信息(?,?,?)}");
cstmt.registerOutParameter(2, Types.INTEGER); //第二个问号输出参数,指定类型
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.setInt(1, 1); //对第一个问号传值
cstmt.execute();
System.out.println(cstmt.getInt(2)); //获取存储过程输出的参数
System.out.println(cstmt.getInt(3));
} catch (ClassNotFoundException | SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
// 6.close
finally {
//有异常发生也会确保释放资源
try {
if (cstmt != null) {
cstmt.close();
cstmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
输出 1 2
4.jdbc处理事务
package com.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.concurrent.Callable;
public class TestTransaction {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// 1.load driver
Class.forName("oracle.jdbc.OracleDriver");
// new oracle.jdbc.OracleDriver();
// 2.connect to the database
String url = "jdbc:oracle:thin:@127.0.0.1:1521:orl";
String user = "excise";
String password = "123";
conn = DriverManager.getConnection(url, user, password);
// 3.excute the sql
conn.setAutoCommit(false); // 不自动提交
stmt = conn.createStatement();
// 批处理
stmt.addBatch("insert into sporter values(1009,'sam','男','数学系')");
stmt.addBatch("insert into sporter values(1010,'tom','男','数学系')");
stmt.addBatch("insert into sporter values(1011,'game','男','数学系')");
stmt.executeBatch(); //执行批处理
conn.commit(); // 运行成功,事务提交
conn.setAutoCommit(true); // 状态恢复
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
try {//运行过程中出现SQL异常则回滚并恢复状态
if (conn != null) {
conn.rollback();
conn.setAutoCommit(true);
}
} catch (SQLException e1) {
e.printStackTrace();
}
}
// 6.close
finally {
// 有异常发生也会确保释放资源
try {
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
jdbc知识点_百度文库
https://wenku.baidu.com/view/2dd9e51ea76e58fafab00324.html