运用Java连接数据库，实现增删改查

com.qst.dao
ICityDao.java(接口)

import java.sql.SQLException;
import java.util.List;

import com.qst.db.DBUtil;
import com.qst.models.City;

public interface ICityDao {
    public void insert(DBUtil db) throws ClassNotFoundException, SQLException;
    public void update(DBUtil db) throws ClassNotFoundException, SQLException;
    public void delete(DBUtil db) throws ClassNotFoundException, SQLException;
    public List<City> search(DBUtil db);
    public void showInfo(DBUtil db) throws SQLException, ClassNotFoundException;


}

CityLmpl.java

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;

import com.qst.db.DBUtil;
import com.qst.models.City;

public class CityImpl implements ICityDao {

    @Override
    public void insert(DBUtil db) throws ClassNotFoundException, SQLException {
        DBUtil dbu = new DBUtil();
        Connection conn = dbu.getConnection();
        String sql="insert into city(name,countrycode,district,population) values (?,?,?,?)";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,"CLARK");
        pst.setString(2,"NLD");
        pst.setString(3,"CLARK");
        pst.setInt(4,123);
        pst.executeUpdate();
        conn.close();
        pst.close();
        System.out.println("添加成功！");
        System.out.println("关闭数据库！");


    }


    @Override
    public void update(DBUtil db) throws ClassNotFoundException, SQLException {
        DBUtil dbu = new DBUtil();
        Connection conn = dbu.getConnection();
        String sql="update city set name=? where name=?";
        PreparedStatement stm = conn.prepareStatement(sql);
        stm.setString(1,"ZZ");
        stm.setString(2,"CLARK");
        stm.executeUpdate();
        conn.close();
        stm.close();
         System.out.println("修改成功！");

    }

    @Override
    public void delete(DBUtil db) throws ClassNotFoundException, SQLException {
        DBUtil dbu = new DBUtil();
        Connection conn = dbu.getConnection();
        String sql="delete from city where name=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1,"ZZ");
        pst.executeUpdate();
        conn.close();
        pst.close();
        System.out.println("删除成功！");



    }


    @Override
    public void showInfo(DBUtil db) throws SQLException, ClassNotFoundException {
        DBUtil dbu=new DBUtil();
        Connection conn= dbu.getConnection();
        // 第三步：数据库操作描述Statment对象并给与描述语句,PrepareStatement;
//      Statement st = conn.createStatement();
        String sql = "select * from city where Name like 'ZZ'";
        PreparedStatement pst = conn.prepareStatement(sql);
        //pst.setString(1, "Herat");
        //pst.setInt(2, 186800);

        // 第四步：执行查询语句并返回数据集
        // ResultSet rs = st.executeQuery(sql);
        ResultSet rs = pst.executeQuery();
        // System.out.println("成功插入一条数据！");
        // 第五步：使用数据集：
        ResultSetMetaData rsmd = rs.getMetaData();
        //int colNum = rsmd.getColumnCount();
        System.out.println("序号|名称|国家代码|地区|人口");
        while (rs.next()) {
            City city = new City();//创建实体类，映射到数据库记录

            city.setId(rs.getInt("id"));
            city.setName(rs.getString("name"));
            city.setDistrict(rs.getString("district"));
            city.setPopulation(rs.getInt("population"));
            city.setCountryCode(rs.getString("countrycode"));


            city.showInfo();//输出City实例里面的所有属性数据

        }

        rs.close();
        // st.close();
        pst.close();
        conn.close();

    }

    @Override
    public List<City> search(DBUtil db) {
        // TODO Auto-generated method stub
        return null;
    }


}

com.qst.db
DBUtil.java

package com.qst.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
    String Driver = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://localhost:33066/world?useSSL=false&useUnicode=true&characterEncoding=utf-8";
    String user = "root";
    String password = "123456";
    Connection conn = null;

    // 方法作用：连接数据库并且得到数据连接
    public Connection getConnection() throws ClassNotFoundException, SQLException {

        // 第一步：选择mysql驱动
        Class.forName(Driver);

        // 第二步：创建数据连接
        Connection conn = DriverManager.getConnection(url, user, password);
        System.out.println("成功连接数据库！");
        return conn;
    }

}

ClassMain.java

package com.qst.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import com.qst.dao.CityImpl;
import com.qst.dao.ICityDao;
import com.qst.models.City;

public class ClassMain {

    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        ICityDao city=new CityImpl();
        DBUtil db=new DBUtil();
//      city.insert(db);
        city.showInfo(db);
//      city.update(db);
//      city.delete(db);
/*      // 连接数据库JDBC的操作
        String Driver = "com.mysql.jdbc.Driver";
        String url = "jdbc:mysql://localhost:3306/world?useSSL=false&useUnicode=true&characterEncoding=utf-8";
        String user = "root";
        String password = "123456";

        try {
            // 第一步：选择mysql驱动
            Class.forName(Driver);

            // 第二步：创建数据连接
            Connection conn = DriverManager.getConnection(url, user, password);
            System.out.println("成功连接数据库！");

            // 第三步：数据库操作描述Statment对象并给与描述语句,PrepareStatement;
//          Statement st = conn.createStatement();
            String sql = "select * from city where name=? and population=?";
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setString(1, "Herat");
            pst.setInt(2, 186800);

            // 第四步：执行查询语句并返回数据集
            // ResultSet rs = st.executeQuery(sql);
            ResultSet rs = pst.executeQuery();
            // System.out.println("成功插入一条数据！");
            // 第五步：使用数据集：
            ResultSetMetaData rsmd = rs.getMetaData();
            int colNum = rsmd.getColumnCount();
            System.out.println("序号|名称|国家代码|地区|人口");
            while (rs.next()) {
                City city = new City();//创建实体类，映射到数据库记录

                city.setId(rs.getInt("id"));
                city.setName(rs.getString("name"));
                city.setDistrict(rs.getString("district"));
                city.setPopulation(rs.getInt("population"));
                city.setCountryCode(rs.getString("countrycode"));


                city.showInfo();//输出City实例里面的所有属性数据

            }

            rs.close();
            // st.close();
            pst.close();
            conn.close();
            System.out.println("关闭数据库！");
        } catch (Exception e) {
            e.printStackTrace();
        }*/

    }

}

com.qst.models
City.java

package com.qst.models;

public class City {
    private int id;
    private String name;
    private String countryCode;
    private String district;
    private int population;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getCountryCode() {
        return countryCode;
    }
    public void setCountryCode(String countryCode) {
        this.countryCode = countryCode;
    }
    public String getDistrict() {
        return district;
    }
    public void setDistrict(String district) {
        this.district = district;
    }
    public int getPopulation() {
        return population;
    }
    public void setPopulation(int population) {
        this.population = population;
    }

    public void showInfo() {
        //System.out.println("序号|名称|国家代码|地区|人口");
        System.out.println(this.getId()+"|"+
                           this.getName()+"|"+
                           this.getCountryCode()+"|"+
                           this.getDistrict()+"|"+
                           this.getPopulation());

    }



}