jdbc就是Java Database Connective的缩写，要使用java程序对数据库进行操作。
在使用JDBC的时候需要用到数据库厂商提供的架包。这里主要是对Mysql和Oracle数据进行将解。mysql主要讲的是注册驱动，和获取连接，后面的步骤和Oracle一样。
mysql：
导入架包
class MysqlDemo{
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://(IP地址，注：IP地址是绑定的，不是动态绑定的)localhost:3306/数据库名称，root(登入用户名),123(密码)"
public static void main(String[] args){
/ /注册驱动
Class.forName(DRIVER);
// 获取连接
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db","root","123");
// 获取执行sql语句对象
Statement stam = conn.createStatement();
ResultSet rs = stam.executeQuery("查询语句");
while(rs.next()){
}
rs.close();
stam.close();
conn.close();
}
}
上面是mysql的规范。
下面介绍Oracle的规范：
package com.zhaoqian.jdbc.p1;
import java.sql.Connection;
import java.sql.DriverManager;
public class Demo1 {
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String USER = "scott";
private static final String PASSWORD = "TIGER";
public static void main(String[] args) throws Exception {
// 加载驱动
Class.forName(DRIVER);
// 获取链接
Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
System.out.println(conn);
}
}
下面是JDBC的简单应用（Statement）
package com.zhaoqiang.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class JDBCUtil {
private static Connection conn;
private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
private static final String USER = "scott";
private static final String PASSWORD = "TIGER";
// 静态代码块加载驱动
static {
try {
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
// 返回链接对象
public static Connection getConnection(){
try {
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return conn;
}
public static void close(){
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
throw new RuntimeException("关闭连接失败，请重试");
}
}
}
}
________________________________
package com.zhaoqiang.util;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.zhaoqiang.vo.Person;
public class JDBCTable {
private static Statement stam;
private static int len = 0;
public JDBCTable(Statement stam) {
this.stam = stam;
}
// 添加数据,修改数据，删除数据
public static int getexecuteUpdate(String sql){
try {
len = stam.executeUpdate(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return len;
}
// 查询数据
public static List<Person> getexecuteQuery(String sql) throws Exception{
ResultSet rs = stam.executeQuery(sql);
Person per = null;
ArrayList<Person> ar = new ArrayList<Person>();
while(rs.next()){
per = new Person();
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
ar.add(per);
}
return ar;
}
}
___________________________________
package com.zhaoqian.jdbc.p1;
import java.sql.Connection;
import java.sql.Statement;
import com.zhaoqiang.util.JDBCTable;
import com.zhaoqiang.util.JDBCUtil;
public class Demo2 {
public static void main(String[] args) {
// 测试Statement类
Connection conn = JDBCUtil.getConnection();
try {
Statement stam = conn.createStatement();
JDBCTable jd = new JDBCTable(stam);
String sql = "DELETE FROM person WHERE ENAME='张三'";
int len = jd.getexecuteUpdate(sql);
System.out.println(len);
} catch (Exception e) {
e.printStackTrace();
}
}
}
然而在开发中，我们并不会使用Statement类，而是使用期子类PreparedStatement来执行sql语句，原因：
1,可以让执行sql语句更加安全。（让运行时的错误在编译时出现，或者说会先运行一遍SQL语句）
2,可以执行一些Statement类无法执行的敏感字符
范例：
package com.zhaoqian.jdbc.p1;
import java.sql.Connection;
import java.sql.Statement;
import com.zhaoqiang.util.JDBCTable;
import com.zhaoqiang.util.JDBCUtil;
public class Demo2 {
public static void main(String[] args) {
// 测试Statement类
Connection conn = JDBCUtil.getConnection();
try {
Statement stam = conn.createStatement();
JDBCTable jd = new JDBCTable(stam);
String sql = "DELETE FROM person WHERE ENAME='mr'SMITH'"; // 敏感字符
int len = jd.getexecuteUpdate(sql);
System.out.println(len);
} catch (Exception e) {
e.printStackTrace();
}
}
}
事务的回滚：当批量执行SQL语句的时候，其中出现了一个错误，我们需要让前面添加的数据全部回滚：
由于在java程序中数据库的事务是自动提交的，如果要手工提交，那么要先取消java的自动提交功能
/**
* 实现数据的增加
* @param sql
* @return 返回收到影响的行数
* @throws Exception
*/
public static int[] doCreate() throws Exception{
Statement stm=conn.createStatement();
int result[] =null;
conn.setAutoCommit(false);//取消自动提交
try{
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextval,'SMITH',23)");
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextval,'SMITH',23)");
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextval,'SMITH',23)");
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextvalwee,'SMITH',23)");
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextval,'SMITH',23)");
stm.addBatch("INSERT INTO person(pid,name,age) VALUES(myseq.nextval,'SMITH',23)");
result =stm.executeBatch();
conn.commit();//事务提交
}catch(Exception e){
conn.rollback();
e.printStackTrace();
}
return result;
}
下面介绍一下PreparedStatement的简单应用：
package com.zhaoqiang.util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.zhaoqiang.vo.Person;
public class JDBCTable_P {
private static Connection conn = null;
public JDBCTable_P(Connection conn) {
this.conn = conn;
}
/**
* 数据库插入操作
* @return 受到作用的行数
* @throws Exception
*/
public static int doInsert() throws Exception{
String sql = "INSERT INTO preson(pid,name,age) VALUES(myseq.nextval,?,?)";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setString(1, "张三");
prs.setInt(2, 28);
return prs.executeUpdate();
}
/**
* 数据库修改（更新）操作
* @param age
* @param name
* @return 受到作用的行数
* @throws Exception
*/
public static int doUpDate(int age,String name) throws Exception{
String sql = "UPDATE person SET AGE = ? WHERE name=?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setInt(1, 50);
prs.setString(2, "张三");
return prs.executeUpdate();
}
/**
* 数据删除操作
* @param age
* @return 受到作用的行数
* @throws Exception
*/
public static int doRemove(int age) throws Exception{
String sql = "DELETE FROM person WHERE age = ?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setInt(1, 50);
return prs.executeUpdate();
}
/**
* 数据库单行查询操作
* @return Person对象
* @throws Exception
*/
public static Person doFind() throws Exception{
String sql = "SELECT pid,name,age FROM person WHERE age=?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setInt(1, 50);
ResultSet rs = prs.executeQuery();
Person per = new Person();
while(rs.next()){
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
}
return per;
}
/**
* 数据库查询全部数据
* @return 一个装着Person对象的List集合
* @throws Exception
*/
public static List<Person> doFindAll() throws Exception{
String sql = "SELECT pid,name,age FROM person";
PreparedStatement prs = conn.prepareStatement(sql);
ResultSet rs = prs.executeQuery();
Person per = null;
ArrayList<Person> list = new ArrayList<Person>();
while(rs.next()){
per = new Person();
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
list.add(per);
}
return list;
}
/**
* 数据库模糊查询
* @param str
* @return 一个装着Person对象的List集合
* @throws Exception
*/
public static List<Person> doFindAll(String str) throws Exception{
String sql = "SELECT pid,name,age FROM person WHERE name LIKE ?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setString(1, "%"+str+"%");
ResultSet rs = prs.executeQuery();
Person per = null;
ArrayList<Person> list = new ArrayList<Person>();
while(rs.next()){
per = new Person();
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
list.add(per);
}
return list;
}
/**
* 数据库分页查询操作
* @param currentPage 当前页
* @param lineSize 每页的行数
* @return 一个装着Person对象的List集合
* @throws Exception
*/
public static List<Person> doFindAll(int currentPage,int lineSize) throws Exception{
String sql = "SELECT * FROM "
+ " ( SELECT ROWNUM rn,pid,name,age FROM person WHERE rn<=?)temp "
+ " WHERE temp.rn>=?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setInt(1, currentPage*lineSize);
prs.setInt(2, (currentPage-1)*lineSize+1);
ResultSet rs = prs.executeQuery();
Person per = null;
ArrayList<Person> list = new ArrayList<Person>();
while(rs.next()){
per = new Person();
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
list.add(per);
}
return list;
}
/**
* 数据库模糊分页查询
* @param currentPage 当前页
* @param lineSize 每页的行数
* @param str 模糊查询的关键字
* @return 一个装着Person对象的List集合
* @throws Exception
*/
public static List<Person> doFindAll(int currentPage,int lineSize,String str) throws Exception{
String sql = "SELECT * FROM ("
+ " SELECT ROWNUM rn,EMPNO,ENAME,JOB,SAL FROM ("
+ " SELECT * FROM EMP WHERE ENAME LIKE ?)"
+ " WHERE ROWNUM <= ?)temp "
+ " WHERE temp.rn>=?";
PreparedStatement prs = conn.prepareStatement(sql);
prs.setString(1, "%"+str+"%");
prs.setInt(2, currentPage*lineSize);
prs.setInt(3, (currentPage-1)*lineSize+1);
ResultSet rs = prs.executeQuery();
Person per = null;
ArrayList<Person> list = new ArrayList<Person>();
while(rs.next()){
per = new Person();
per.setPid(rs.getInt(1));
per.setName(rs.getString(2));
per.setAge(rs.getInt(3));
list.add(per);
}
return list;
}
}
_____________________________
package com.zhaoqian.jdbc.p1;
import java.security.KeyStore.ProtectionParameter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import com.zhaoqiang.util.JDBCTable_P;
import com.zhaoqiang.util.JDBCUtil;
public class Demo3 {
public static void main(String[] args) throws Exception {
Connection conn = JDBCUtil.getConnection();
JDBCTable_P jdbc = new JDBCTable_P(conn);
jdbc.doInsert();
jdbc.doUpDate(50, "张三");
jdbc.doFindAll();
}
}
