JDBC

收藏小管 2017-07-17

展開全文

JDBC（Java Data Base Connection）的作用是連接數(shù)據(jù)庫

先看下jdbc連接SQLServer數(shù)據(jù)庫的簡單例子

代碼實(shí)現(xiàn)（FirstJDBC)：

package com.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class FirstJDBC {
public static void main(String[] args)
{
//調(diào)用連接數(shù)據(jù)庫的操作
Connection con = createConnection();
}
/**
* JDBC 建立 SQL Server數(shù)據(jù)庫連接
*/
private static Connection createConnection() {
//定義加載驅(qū)動(dòng)程序
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
//定義連接服務(wù)器和數(shù)據(jù)庫sample
String dbURL = "jdbc:sqlserver://localhost:1433; DataBaseName = sample1" ;
//默認(rèn)用戶名，不要用windows默認(rèn)身份驗(yàn)證
String userName = "sa" ;
String userPassword = "zhichao" ;
Connection connection = null ;
Statement sta = null ;
try {
//正式加載驅(qū)動(dòng)
Class.forName(driverName);
//開始連接
connection = DriverManager.getConnection(dbURL, userName, userPassword);
System.out.println("Connection Success !");
//向數(shù)據(jù)庫中執(zhí)行SQL語句
sta = connection.createStatement() ;
ResultSet rs = sta.executeQuery("SELECT id,name,height From Table_1");
while(rs.next())
{
int id = rs.getInt("id");
String name = rs.getString("name");
float height = rs.getFloat("height");
System.out.println("id = "+id+" name = "+name+" height = "+height);
}
} catch (Exception e) {
System.out.println("Connection Fail !");
e.printStackTrace() ;
}
/**
* 關(guān)閉數(shù)據(jù)庫
* @param connection
*/
finally
{
try {
if (null != sta)
{
sta.close() ;
sta = null;
System.out.println("Statement 關(guān)閉成功");
}
if (null != connection)
{
connection.close() ;
connection = null;
System.out.println("Connection 關(guān)閉成功");
}
} catch (Exception e) {
e.printStackTrace() ;
}
}
return connection ;
}
}

小結(jié)：

要寫一個(gè)jdbc程序，先要加載相應(yīng)數(shù)據(jù)庫的驅(qū)動(dòng)程序，驅(qū)動(dòng)程序最好放在你建的工程里面，可以在你的工程下面建一個(gè) lib文件夾以存儲外部的jar文件，這樣的話把你的工程拷貝到別的計(jì)算機(jī)運(yùn)行，仍能成功執(zhí)行。

jdbc代碼一般步驟：

1）加載外部驅(qū)動(dòng)程序（jar包）

2）正式加載驅(qū)動(dòng)程序（Class.forName(driverName) ）

3）獲取connection連接（在jdk中的sql包中，只提供了一個(gè)類那就是DriverManeger,通過調(diào)用它的靜態(tài)方法getConnection(),可以得到以數(shù)據(jù)庫的連接

4）創(chuàng)建sql語句的聲明（Statement），執(zhí)行sql語句（查詢），遍歷結(jié)果集

5）關(guān)閉數(shù)據(jù)庫連接（一般用finally{}來處理，或者調(diào)用方法的形式來完成，關(guān)閉之前先判斷你要關(guān)閉的對象連接是否為空，如果空那會拋異常，所以先判斷）

------------------------------------- ------------------------------------- ------------------------Data Access Objects-------------------- ------------------------------------------- ---------------------------

使用 DAO模式來對數(shù)據(jù)庫做增刪改查操作

這種模式可以大概分為三個(gè)層：1.DAO層 2.服務(wù)層 3.表現(xiàn)層

1）表現(xiàn)層：相當(dāng)于客戶端用來查看，提交信息的角色

2）服務(wù)層：是表現(xiàn)層和DAO層的紐帶，其實(shí)也沒干什么事就是通知消息的角色

3）DAO ：真正要做事的角色（對數(shù)據(jù)庫的某些操作）

舉個(gè)生活中的例子：

就好比你去餐廳吃飯，你充當(dāng)一個(gè) （表現(xiàn)層）的角色，然后有美女服務(wù)員（服務(wù)層），問你需要吃什么東西，給你下一張訂單，讓你填。之后服務(wù)員把訂單傳到廚師（DAO層）那里，具體操作廚師會搞定，一段時(shí)間后廚師把做好的食物傳給服務(wù)員，服務(wù)員把食物在傳給客戶，這些操作就算基本完成了。

執(zhí)行順序：表現(xiàn)層-->服務(wù)層-->DAO層-->返回服務(wù)層-->返回表現(xiàn)層

來看看實(shí)現(xiàn)DAO模式的UML圖：

代碼實(shí)現(xiàn)：

1.Bean文件，在這主要作用(有點(diǎn)像中介存儲的角色)：當(dāng)從數(shù)據(jù)庫拿出數(shù)據(jù)后，一個(gè)個(gè)set到該類里，進(jìn)行賦值，然后把該對象放到集合中，之后再get出來

Student.Java

[java] view plain copy

package com.myjdbc.bean;
public class Student {
private Integer stuId;
private String stuName ;
private Integer stuAge;
private String stuTel ;
private String stuAddress ;
private Integer groupId;
public Integer getStuId() {
return stuId;
}
public void setStuId(Integer stuId) {
this.stuId = stuId;
}
public String getStuName() {
return stuName;
}
public void setStuName(String stuName) {
this.stuName = stuName;
}
public Integer getStuAge() {
return stuAge;
}
public void setStuAge(Integer stuAge) {
this.stuAge = stuAge;
}
public String getStuTel() {
return stuTel;
}
public void setStuTel(String stuTel) {
this.stuTel = stuTel;
}
public String getStuAddress() {
return stuAddress;
}
public void setStuAddress(String stuAddress) {
this.stuAddress = stuAddress;
}
public Integer getGroupId() {
return groupId;
}
public void setGroupId(Integer groupId) {
this.groupId = groupId;
}
}

2.java連接數(shù)據(jù)庫的基本操作及關(guān)閉，封裝在一個(gè)類中

JDBCUtils.java

[java] view plain copy

package com.myjdbc.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCUtils {
/**
* 獲取連接
*
*/
public static Connection getConnection()
{
String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://localhost:1433; DataBaseName = studentManager";
String user = "sa" ;
String password = "zhichao";
Connection con = null ;
try {
Class.forName(driverName);
con = DriverManager.getConnection(url, user, password);
System.out.println("success");
} catch (Exception e) {
e.printStackTrace();
}
return con ;
}
/**
* 關(guān)閉連接
*/
public static void free(ResultSet rs, Statement sta , Connection con)
{
try {
if(null != rs)
{
rs.close();
rs = null ;
}
if(null != sta)
{
sta.close();
sta = null ;
}
if(null != con)
{
con.close();
con = null ;
}
} catch (Exception e) {
e.printStackTrace();
}
}
}

3.定義一個(gè)DAO接口

StudentDAO.java

[java] view plain copy

package com.myjdbc.dao;
import java.util.Set;
import com.myjdbc.bean.Student ;
public interface StudentDAO {
public int addStudent(Student student) ;
public int deleteStudent(String name);
public int updateStudent(String name);
public Student findStudent(String name);
public Set<Student> findAll();
}

4.實(shí)現(xiàn)DAO接口的類，具體DAO,做重要工作的類

ConcreteStudentDao.java

[java] view plain copy

package com.myjdbc.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.util.Set;
import com.myjdbc.bean.Student;
import com.myjdbc.dao.StudentDAO;
import com.myjdbc.utils.JDBCUtils;
public class ConcreteStudentDao implements StudentDAO{
//增加一個(gè)學(xué)生
public int addStudent(Student student)
{
Connection con = null ;
PreparedStatement ps = null ;
int i = 0 ;
try
{
con = JDBCUtils.getConnection();
String sql = "insert into student(stuName,stuAge,stuTel,stuAddress,groupId) values(?,?,?,?,?)";
ps = con.prepareStatement(sql);
ps.setString(1, student.getStuName());
ps.setInt(2, student.getStuAge());
ps.setString(3, student.getStuTel());
ps.setString(4, student.getStuAddress());
ps.setInt(5, student.getGroupId());
i = ps.executeUpdate() ;
}
catch(SQLException e)
{
throw new DAOException(e.getMessage(),e);
}
finally
{
JDBCUtils.free(null, ps, con);
}
return i;
}
//刪除一個(gè)學(xué)生
public int deleteStudent(String name)
{
Connection con = null ;
PreparedStatement ps = null ;
int i = 0 ;
try
{
con = JDBCUtils.getConnection();
String sql = "delete from student where stuName =?";
ps = con.prepareStatement(sql);
ps.setString(1, name);
i = ps.executeUpdate() ;
}
catch(SQLException e)
{
throw new DAOException(e.getMessage(),e);
}
finally
{
JDBCUtils.free(null, ps, con);
}
return i;
}
//修改一個(gè)學(xué)生
public int updateStudent(String name)
{
Connection con = null ;
PreparedStatement ps = null ;
int i = 0 ;
try
{
con = JDBCUtils.getConnection();
String sql = "update student set stuAge=stuAge+1 where stuName =?";
ps = con.prepareStatement(sql);
ps.setString(1, name);
i = ps.executeUpdate() ;
}
catch(SQLException e)
{
throw new DAOException(e.getMessage(),e);
}
finally
{
JDBCUtils.free(null, ps, con);
}
return i;
}
//查詢一行
public Student findStudent(String name)
{
Connection con = null ;
PreparedStatement ps = null ;
Student stu = null ;
ResultSet rs = null;
try
{
con = JDBCUtils.getConnection();
String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student where stuName =?";
ps = con.prepareStatement(sql);
ps.setString(1, name);
rs = ps.executeQuery() ;
stu = new Student();
while(rs.next())
{
stu.setStuName(rs.getString(1));
stu.setStuAge(rs.getInt(2));
stu.setStuTel(rs.getString(3));
stu.setStuAddress(rs.getString(4));
stu.setGroupId(rs.getInt(5));
}
}
catch(SQLException e)
{
throw new DAOException(e.getMessage(),e);
}
finally
{
JDBCUtils.free(rs, ps, con);
}
return stu;
}
//查詢所有
public Set<Student> findAll()
{
Connection con = null ;
PreparedStatement ps = null ;
Student stu = null ;
ResultSet rs = null;
Set<Student> set = null ;
try
{
con = JDBCUtils.getConnection();
String sql = "select stuName,stuAge,stuTel,stuAddress,groupId from student";
ps = con.prepareStatement(sql);
set = new HashSet<Student>() ;
rs = ps.executeQuery() ;
while(rs.next())
{
stu = new Student();
stu.setStuName(rs.getString(1));
stu.setStuAge(rs.getInt(2));
stu.setStuTel(rs.getString(3));
stu.setStuAddress(rs.getString(4));
stu.setGroupId(rs.getInt(5));
set.add(stu);
}
}
catch(SQLException e)
{
throw new DAOException(e.getMessage(),e);
}
finally
{
JDBCUtils.free(rs, ps, con);
}
return set;
}
}

5.自定義異常繼承了運(yùn)行時(shí)異常，具體操作讓父類實(shí)現(xiàn)

DAOException.java

[java] view plain copy

package com.myjdbc.dao;
/**
* 自定義異常
* @author Administrator
*
*/
public class DAOException extends RuntimeException {
public DAOException()
{
super();
}
public DAOException(String messege,Throwable cause)
{
super(messege,cause);
}
public DAOException(String messege)
{
super(messege);
}
public DAOException(Throwable cause)
{
super(cause);
}
}

6定義一個(gè)服務(wù)類（服務(wù)層），本來還要定義一個(gè)接口，這里簡寫了，客戶與DAO的紐帶，持有DAO對象的引用

StudentService.java

[java] view plain copy

package com.myjdbc.service;
import java.util.Set;
import com.myjdbc.bean.Student;
import com.myjdbc.dao.StudentDAO;
import com.myjdbc.dao.ConcreteStudentDao;
public class StudentService {
StudentDAO sd = new ConcreteStudentDao();
public int add(Student student)
{
return this.sd.addStudent(student);
}
public int delete(String name)
{
return this.sd.deleteStudent(name);
}
public int update(String name)
{
return this.sd.updateStudent(name);
}
public Student find(String name)
{
return this.sd.findStudent(name);
}
public Set<Student> findAll()
{
return this.sd.findAll();
}
}

7.定義一個(gè)測試類，相當(dāng)于（表現(xiàn)層）

Client.java

[java] view plain copy

package com.myjdbc.test;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Set;
import com.myjdbc.bean.Student;
import com.myjdbc.service.StudentService;
public class Client {
public static void main(String[] args)
{
Student stu = new Student();
Set<Student> set = new HashSet<Student>();
// stu.setStuName("zhangsan");
// stu.setStuAge(20);
// stu.setStuTel("18779157911");
// stu.setStuAddress("china");
// stu.setGroupId(1);
StudentService ss = new StudentService();
//System.out.println(ss.add(stu));
//System.out.println(ss.delete("aa"));
//System.out.println(ss.update("bb"));
//stu = ss.find("cc");
//System.out.println(stu.getStuName() +" " +stu.getStuAge()+" "+stu.getStuTel()+" "+stu.getStuAddress()+" "+stu.getGroupId());
set = ss.findAll() ;
Iterator<Student> iterator = set.iterator();
while(iterator.hasNext())
{
Student student = (Student)iterator.next() ;
System.out.println(student.getStuName() +" " +student.getStuAge()+" "+student.getStuTel()+" "+student.getStuAddress()+" "+student.getGroupId());
}
}
}

本站是提供個(gè)人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：收藏小管 > 《javaweb》

舉報(bào)/認(rèn)領(lǐng)