连接数据库实现增删改查

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(); } } }