有點(diǎn)高產(chǎn)了呵曹。。何暮。
jdbc編程流程
package com.webtest.jdbc;
import java.sql.*;
/**
* Created by elijahliu on 2017/2/18.
*/
public class JDBCTest {
public static void main(String[] args) {
String sql = "SELECT * FROM tbl_user";
//代表當(dāng)前數(shù)據(jù)庫(kù)鏈接
Connection conn = null;
//代表sql語(yǔ)句
Statement st = null;
//代表返回?cái)?shù)據(jù)
ResultSet rs = null;
try {
// Class.forName ("com.mysql.jdbc.Driver");//注冊(cè)mysql驅(qū)動(dòng)
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db","root","123456");//設(shè)置鏈接
st = conn.createStatement();//代表statement套件
rs = st.executeQuery(sql);//執(zhí)行sql語(yǔ)句返回結(jié)果
while (rs.next()) {//逐條讀取數(shù)據(jù)奄喂，直到
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ps:必須在工程依賴中添加mysql-jdbc.jar的包。
基礎(chǔ)操作 增刪改查
package com.webtest.jdbc;
import java.sql.*;
/**
* Created by elijahliu on 2017/2/18.
*/
public class JDBCTest {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "123456");//設(shè)置鏈接
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void insert(){
Connection conn = getConnection();
String sql = "INSERT INTO tbl_user(name,password,email)"+"VALUES('Tom','123456','tom@gmail.com')";
try {
Statement st = conn.createStatement();
int cout = st.executeUpdate(sql);//運(yùn)行插入sql語(yǔ)句海洼，返回受影響條目數(shù)字 跨新，executeUpdate可以執(zhí)行insert update delete ddl語(yǔ)句
System.out.println("向用戶表中插入了" + cout + " 條記錄");
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void update(){
Connection conn = getConnection();
String sql = "UPDATE tbl_user SET email = 'tom@126.com' WHERE name = Tom";
try {
Statement st = conn.createStatement();
int cout = st.executeUpdate(sql);//運(yùn)行插入sql語(yǔ)句，返回受影響條目數(shù)字 贰军，executeUpdate可以執(zhí)行insert update delete ddl語(yǔ)句
System.out.println("向用戶表中更新了" + cout + " 條記錄");
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void delete(){
Connection conn = getConnection();
String sql = "DELETE FROM tbl_user WHERE name = 'Tom'";
try {
Statement st = conn.createStatement();
int cout = st.executeUpdate(sql);//運(yùn)行插入sql語(yǔ)句玻蝌，返回受影響條目數(shù)字 ，executeUpdate可以執(zhí)行insert update delete ddl語(yǔ)句
System.out.println("向用戶表中刪除了" + cout + " 條記錄");
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
String sql = "SELECT * FROM tbl_user";
//代表當(dāng)前數(shù)據(jù)庫(kù)鏈接
Connection conn = null;
//代表sql語(yǔ)句
Statement st = null;
//代表返回?cái)?shù)據(jù)
ResultSet rs = null;
try {
// Class.forName ("com.mysql.jdbc.Driver");//注冊(cè)mysql驅(qū)動(dòng)
conn = getConnection();
st = conn.createStatement();//代表statement套件
rs = st.executeQuery(sql);//執(zhí)行sql語(yǔ)句返回結(jié)果
while (rs.next()) {//逐條讀取數(shù)據(jù)词疼，直到
System.out.println(rs.getInt("id"));
System.out.println(rs.getString("name"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
try {
rs.close();
st.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//插
insert();
update();
delete();
}
}
JDBC編程之事務(wù)處理
事務(wù)
在多用戶對(duì)同一數(shù)據(jù)進(jìn)行同時(shí)操作時(shí)俯树，對(duì)數(shù)據(jù)的更改會(huì)出現(xiàn)不同步的問題，破壞數(shù)據(jù)的一致性贰盗。而事務(wù)就是進(jìn)行并發(fā)控制的基本單位许饿，事務(wù)是一個(gè)操作序列，這些操作要么都執(zhí)行要么都不執(zhí)行舵盈，是一個(gè)不可分割的工作單位陋率。比如銀行轉(zhuǎn)賬的工作球化，一個(gè)賬戶扣錢，一個(gè)賬戶加錢瓦糟，要么都執(zhí)行筒愚，要么都不執(zhí)行。在每個(gè)事務(wù)結(jié)束時(shí)都能保證數(shù)據(jù)的一致性菩浙。
對(duì)于coder來說巢掺，事務(wù)也可以說是一組連續(xù)的sql操作，要么對(duì)數(shù)據(jù)進(jìn)行永久性的更改劲蜻，要么不更改陆淀。
事務(wù)的特征
原子性
事務(wù)中包含的操作被看作一個(gè)邏輯單元，要么全部成功先嬉，要么全部失敗轧苫。事務(wù)的所有元素作為一個(gè)整體提交或回滾。事務(wù)的操作是不可分割的疫蔓。
一致性
事務(wù)開始之前和事務(wù)結(jié)束之后含懊，數(shù)據(jù)庫(kù)都處于一致性狀態(tài)，數(shù)據(jù)庫(kù)完整性沒有被破壞鳄袍。
隔離性
對(duì)數(shù)據(jù)庫(kù)修改的各個(gè)事務(wù)是相互隔離的绢要，獨(dú)立的，不能由一個(gè)事務(wù)而去影響其他事務(wù)拗小。
持久性
是說重罪，事務(wù)完成之后，對(duì)于數(shù)據(jù)的影響是永久的哀九，即使系統(tǒng)出現(xiàn)故障也會(huì)一直保留剿配，真實(shí)的修改了數(shù)據(jù)庫(kù)。
事務(wù)的語(yǔ)句
- 開始事務(wù)：BEGIN TRANSACTION
- 提交事務(wù)：COMMIT TRANSACTION
- 回滾事務(wù): ROLLBACK TRANSACTION
例子：添加id為10的一條成員記錄阅束，再添加一條id為1的地址記錄呼胚，因?yàn)閕d為1的條目已經(jīng)存在，則會(huì)發(fā)生主鍵沖突異常息裸。所以在main中使用conn.rollback進(jìn)行回滾操作蝇更。
package com.webtest.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* Created by elijahliu on 2017/2/18.
*/
public class TransactionTest {
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jsp_db", "root", "123456");
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void insertUserData(Connection conn) throws SQLException {
String sql = "INSERT INTO tbl_user(id,name,password,email)" +
"VALUES(10,'Tom','123456','tom@gmail.com')";
Statement st = null;
st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向用戶表中插入了" + count + "條記錄");
//在這里的最后不需要進(jìn)行conn.close（），在main方法中一起關(guān)掉就好
}
public static void insertAddressData(Connection conn) throws SQLException {
String sql = "INSERT INTO tbl_address(id,city,country,user_id)" +
"VALUES(1,'shanghai''china','10')";
Statement st = conn.createStatement();
int count = st.executeUpdate(sql);
System.out.println("向地址表中插入了" + count + "條記錄");
//在這里的最后不需要進(jìn)行conn.close（）呼盆，在main方法中一起關(guān)掉就好
}
public static void main(String[] args) {
Connection conn = null;
try {
conn = getConnection();
conn.setAutoCommit(false);//禁止事務(wù)的自動(dòng)提交
insertUserData(conn);
insertAddressData(conn);
conn.commit();//提交事務(wù)
} catch (SQLException e) {
System.out.println("===============捕獲到異常================");
e.printStackTrace();
try {
conn.rollback();
System.out.println("===========事務(wù)回回滾成功===============");
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
用戶表
地址表
JDBC之程序優(yōu)化
前面的程序都是在程序中“硬配置”年扩，其實(shí)可以創(chuàng)建一個(gè)配置文件來進(jìn)行存儲(chǔ)，也就是.properties文件访圃。
首先建立dbconfig.properties
driver=com.mysql.jdbc.Driver
dburl=jdbc\:mysql\://localhost\:3306/jsp_db
user=root
password=123456
ConnectionFactory.java
package com.webtest.util;
import com.mysql.jdbc.Driver;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* Created by elijahliu on 2017/2/18.
*/
public class ConnectionFactory {
private static String driver;
private static String dburl;
private static String user;
private static String password;
private Connection conn;
private static final ConnectionFactory factory = new ConnectionFactory();//聲明一個(gè)這個(gè)類的成員變量
static {
Properties prop = new Properties();//定義一個(gè)properties厨幻，適用于儲(chǔ)存文件中的鍵值對(duì)的
try {
InputStream in = ConnectionFactory.class.getClassLoader()
.getResourceAsStream("dbconfig.properties");
prop.load(in);
} catch (Exception e) {
System.out.println("============配置文件讀取錯(cuò)誤=============");
}
driver = prop.getProperty("driver");
dburl = prop.getProperty("dburl");
user = prop.getProperty("user");
password = prop.getProperty("password");
}
private ConnectionFactory(){
}
public static ConnectionFactory getInstance(){
return factory;
}
public Connection makeConnection(){
try {
conn = DriverManager.getConnection(dburl, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
ConnectionFactory.java
package com.webtest.util;
import com.mysql.jdbc.Driver;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
/**
* Created by elijahliu on 2017/2/18.
*/
public class ConnectionFactory {
private static String driver;
private static String dburl;
private static String user;
private static String password;
private Connection conn;
private static final ConnectionFactory factory = new ConnectionFactory();//聲明一個(gè)這個(gè)類的成員變量
static {
Properties prop = new Properties();//定義一個(gè)properties，適用于儲(chǔ)存文件中的鍵值對(duì)的
try {
InputStream in = ConnectionFactory.class.getClassLoader()
.getResourceAsStream("dbconfig.properties");
prop.load(in);
} catch (Exception e) {
System.out.println("============配置文件讀取錯(cuò)誤=============");
}
driver = prop.getProperty("driver");
dburl = prop.getProperty("dburl");
user = prop.getProperty("user");
password = prop.getProperty("password");
}
private ConnectionFactory(){
}
public static ConnectionFactory getInstance(){//在這里使用了單例模式，只允許有一個(gè)Connection鏈接
return factory;
}
public Connection makeConnection(){
try {
conn = DriverManager.getConnection(dburl, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
}
DTO類
data transfer object數(shù)據(jù)傳輸對(duì)象况脆，用于遠(yuǎn)程調(diào)用等對(duì)象需要大量調(diào)用的地方饭宾。
創(chuàng)建三個(gè)DTO類
IdEntity.java
package com.webtest.entity;
/**
* Created by elijahliu on 2017/2/18.
*/
public abstract class IdEntity {
protected long id;
public long getId(){
return id;
}
public void setId(Long id) {
this.id = id;
}
}
//此方法相當(dāng)于核心抽象類
Address.java
package com.webtest.entity;
/**
* Created by elijahliu on 2017/2/18.
*/
public class Address extends IdEntity {
private String city;
private String country;
private String userId;
@Override
public String toString() {
return "Address{" +
"city='" + city + '\'' +
", country='" + country + '\'' +
", userId='" + userId + '\'' +
'}';
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getCountry() {
return country;
}
public void setCountry(String country) {
this.country = country;
}
public String getUserId() {
return userId;
}
public void setUserId(String userId) {
this.userId = userId;
}
}
User.java
package com.webtest.entity;
import com.sun.javafx.geom.transform.Identity;
/**
* Created by elijahliu on 2017/2/18.
*/
public class User extends IdEntity {
private String name;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getEmaill() {
return emaill;
}
public void setEmaill(String emaill) {
this.emaill = emaill;
}
private String password;
private String emaill;
@Override
public String toString() {
return "User{" +
"name='" + name + '\'' +
", password='" + password + '\'' +
", emaill='" + emaill + '\'' +
'}';
}
}
DAO類
data access object數(shù)據(jù)訪問對(duì)象，主要是用來封裝數(shù)據(jù)庫(kù)中的表格了，將數(shù)據(jù)庫(kù)中的數(shù)據(jù)轉(zhuǎn)化為DTO類看铆。
創(chuàng)建UserDao、 dao/impl/UserDaoimpl笆搓、
com.webtest.Test/UserDaoTest.java三個(gè)文件性湿。
UserDao.interface
package com.webtest.dao;
import com.webtest.entity.User;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Created by elijahliu on 2017/2/18.
*/
public interface UserDao {
public void save(Connection conn, User user) throws SQLException;
public void update(Connection conn,Long id,User user) throws SQLException;
public void delete(Connection conn, User user) throws SQLException;
//需要編寫相應(yīng)的實(shí)現(xiàn)，這是典型的面向接口的編程满败，接口只定義外觀和行為
}
UserDaoimpl.java (UserDao的實(shí)現(xiàn)類)
package com.webtest.dao.impl;
import com.webtest.dao.UserDao;
import com.webtest.entity.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* Created by elijahliu on 2017/2/18.
*/
public class UserDaoimpl implements UserDao {
/*
* 保存用戶信息
* */
@Override
public void save(Connection conn, User user) throws SQLException {
PreparedStatement ps = conn.prepareCall("INSERT INTO tbl_user(name,password,email) VALUES (?,?,?)");
ps.setString(1,user.getName());
ps.setString(2, user.getPassword());
ps.setString(3,user.getEmaill());
ps.execute();
//這里使用的PreparedStatement是jdbc用來執(zhí)行的sql查詢語(yǔ)句的api之一，用于執(zhí)行參數(shù)化的查詢
//叹括？是占位符算墨，下面的setString方法，是用來替換占位符的汁雷。將user對(duì)象中的數(shù)據(jù)填補(bǔ)到sql語(yǔ)句中
//從而進(jìn)行正確的查詢净嘀，所以最后ps.execute() 執(zhí)行。
}
/*
*
* 根據(jù)指定的id更新用戶信息
* */
@Override
public void update(Connection conn, Long id, User user) throws SQLException {
PreparedStatement ps = conn.prepareStatement("UPDATE tbl_user SET name=?,password=?,email=? WHERE id=?");
ps.setString(1, user.getName());
ps.setString(2, user.getPassword());
ps.setString(3, user.getEmaill());
ps.setLong(4, id);
ps.execute();
}
/*
*
* 刪除制定的用戶信息
*
* */
@Override
public void delete(Connection conn, User user) throws SQLException {
PreparedStatement ps = conn.prepareStatement("DELETE FROM tbl_user WHERE id=?");
ps.setLong(1, user.getId());
ps.execute();
}
}
UserDaoTest.java
package com.webtest.Test;
import com.webtest.dao.UserDao;
import com.webtest.dao.impl.UserDaoimpl;
import com.webtest.entity.User;
import com.webtest.util.ConnectionFactory;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Created by elijahliu on 2017/2/18.
*/
public class UserDaoTest {
public static void main(String[] args) {
Connection conn = null;
try {
conn = ConnectionFactory.getInstance().makeConnection();
conn.setAutoCommit(false);//關(guān)閉連接事務(wù)的自動(dòng)提交
UserDao userDao = new UserDaoimpl();
User tom = new User();
tom.setName("Tom");
tom.setPassword("123456");
tom.setEmaill("tom@gmail.com");
userDao.save(conn,tom); //調(diào)用持久層存儲(chǔ)
conn.commit();//事務(wù)提交
} catch (SQLException e) {
try {
conn.rollback();//存在異常則進(jìn)行事務(wù)回滾
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}
}
}
