連接配接資料庫實作增删改查

1.建立dbconfig.properties檔案

2.建立資料庫連接配接工廠

3.建立entity對象實體類

4.建立Dao接口

5.建立實作Dao接口的實作類，寫入具體實作功能

6.建立測試類實作main方法

1.driver=com.mysql.jdbc.Driver

dburl=jdbc\:mysql\://localhost\:3306/jikedb

user=root

password=123456

2.package ConnectionControl; import java.sql.Connection; import java.io.InputStream; import java.sql.DriverManager; import java.util.Properties; public class ConnectionFactory { private static String driver; private static String dburl; private static String user; private static String password; private static final ConnectionFactory factory = new ConnectionFactory(); private ConnectionFactory(){} //建立一個getInstance()擷取ConnectionFactory的一個執行個體  為了保證對象是單例的 為單利模式 public static ConnectionFactory getInstance(){ return factory; } static{ //定義一個Properties類 繼承hashTable 可以用來處理檔案中存儲的鍵值對 Properties prop = new Properties(); try { //通過類加載器的getResourceAsStream()方法将屬性檔案中的内容讀取到一個輸入流中 InputStream in = ConnectionFactory.class.getClassLoader() .getResourceAsStream("dbconfig.properties"); //從輸入流中讀取鍵值對清單 prop.load(in); } catch (Exception e) { System.out.println("=====配置檔案讀取錯誤======"); e.printStackTrace(); } //将讀取到的值給變量 driver = prop.getProperty("driver"); dburl = prop.getProperty("dburl"); user = prop.getProperty("user"); password = prop.getProperty("password"); } //用來儲存資料庫連接配接 private Connection conn; //建立連接配接資料庫的方法 public Connection makeConnection(){ try { Class.forName(driver); conn=DriverManager.getConnection(dburl,user,password); } catch (Exception e) { e.printStackTrace(); } return conn; } }

3.package com.hand.entity; public class Address { private long adress_id; private String city; private String country; private long id; public long getAdress_id() { return adress_id; } public void setAdress_id(long adress_id) { this.adress_id = adress_id; } 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 long getId() { return id; } public void setId(long id) { this.id = id; } @Override public String toString() { return "Address [address_id=" + adress_id + ", city=" + city + ", country=" + country + ", id=" + id + "]"; } public Address(long id) { super(); this.id = id; } public Address(){ } }

4.package com.hand.dao; import java.sql.Connection; import java.sql.SQLException; import com.hand.entity.Address; public interface AddressDao { public void insert(Connection conn,Address address) throws SQLException; public void update(Connection conn,Address address) throws SQLException; public void delete(Connection conn,Address address) throws SQLException; public void select(Connection conn,Address address) throws SQLException; }

5.package com.hand.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import com.hand.dao.AddressDao; import com.hand.entity.Address; public class AddressDaoImpl implements AddressDao { //插入 @Override public void insert(Connection conn, Address adress) throws SQLException { String str  = "insert into address (adress_id,city,country,id) values (?,?,?,?)"; PreparedStatement ps = conn.prepareStatement(str); ps.setLong(1, adress.getAdress_id()); ps.setString(2,adress.getCity()); ps.setString(3,adress.getCountry()); ps.setLong(4, adress.getId()); int count = ps.executeUpdate(); System.out.println("===向使用者表中插入了" + count + "條記錄==="); } //删除 @Override public void delete(Connection conn, Address address) throws SQLException { String str = "delete from address where id=?"; PreparedStatement ps = conn.prepareStatement(str); ps.setLong(1, address.getId()); int count = ps.executeUpdate(); System.out.println("===已删除位址表中"+count+"條資料"); } //更新 @Override public void update(Connection conn, Address address) throws SQLException { String str = "update address set city=? where id=?"; PreparedStatement ps = conn.prepareStatement(str); ps.setString(1, address.getCity()); ps.setLong(2, address.getId()); int count = ps.executeUpdate(); System.out.println("===已向位址表中更新了"+count+"條資料==="); } //查詢 @Override public void select(Connection conn, Address address) throws SQLException { String str = " select * from address"; PreparedStatement ps = conn.prepareStatement(str); ResultSet rs = ps.executeQuery(); while(rs.next()){ System.out.print(rs.getLong("adress_id")+" "); System.out.print(rs.getString("city")+" "); System.out.print(rs.getString("country")+" "); System.out.println(rs.getLong("id")+" "); } System.out.println("===查詢完畢==="); } }

6.package com.hand.dao.impl; import java.sql.Connection; import java.sql.SQLException; import com.hand.dao.AddressDao; import com.hand.entity.Address; import ConnectionControl.ConnectionFactory; public class AddressDaoImplTest { public static void main(String[] args) { Connection conn=null; try { conn=ConnectionFactory.getInstance().makeConnection(); conn.setAutoCommit(false); AddressDao addressDao = new AddressDaoImpl(); //                         //                        Address address = new Address(); //                        address.setAdress_id(2); //                        address.setCity("beijing"); //                        address.setCountry("china"); //                        address.setId(2); //                        addressDao.insert(conn, address); Address address = new Address(); addressDao.select(conn, address); //                         //                        Address address = new Address(2); //                        address.setCity("guangzhou"); //                        addressDao.update(conn, address); //                         //                        Address address = new Address(2); //                        addressDao.delete(conn, address); conn.commit(); } catch (Exception e) { e.printStackTrace(); //若有異常則進行事物復原 try { conn.rollback(); System.out.println("===已進行事物復原==="); } catch (Exception e2) { e2.printStackTrace(); } } try { conn.close(); System.out.println("done"); } catch (SQLException e) { e.printStackTrace(); } } }