JDBC连接数据库并实现增删查改

JDBC连接数据库并实现数据库操作

package com.thunisoft.yhan.daos;

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

public class BaseDao {

    protected Connection getConn() {
        String url = "jdbc:ArteryBase://localhost:5432/test?ApplicationName=user&Charset=utf8";
        String username = "sa";
        String password = "test";
        String driver = "com.thunisoft.ArteryBase.Driver";
        //以上部分根据情况自己写，mysql就写mysql的
        try {
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, username, password);
            return conn;
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return null;
    }

    protected void closeAll(ResultSet rs, Connection conn, Statement s, PreparedStatement ps) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (conn != null) {
                conn.close();
            }
            if (s != null) {
                s.close();
            }
            if (ps != null) {
                ps.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    /**
     * 封装增删改操作，查操作要返回结果集，所以单独处理，见示例
     * @param sql
     * @param objects
     * @return
     */
    public boolean executeSql(String sql,Object ...objects){
        Connection conn=getConn();
        PreparedStatement ps=null;
        try {
            ps=conn.prepareStatement(sql);
            for(int i=0;i<objects.length;i++){
                ps.setString(i+1, objects[i].toString());
            }
            int result=ps.executeUpdate();
            return result!=0;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(null, conn, null, ps);
        }
        return true;
    }
}

使用方法

假设有一个user类：

package com.thunisoft.yhan.pojos;

public class User {
    private String id;
    private String name;
    private String password;
    private String provinceId;
    public User() {
        super();
        // TODO Auto-generated constructor stub
    }

    public User(String id, String name, String password, String provinceId) {
        super();
        this.id = id;
        this.name = name;
        this.password = password;
        this.provinceId = provinceId;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }

    public String getProvinceId() {
        return provinceId;
    }

    public void setProvinceId(String provinceId) {
        this.provinceId = provinceId;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", password=" + password + ", provinceId=" + provinceId + "]";
    }


}

对user的增删查改操作：

package com.thunisoft.yhan.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.thunisoft.yhan.pojos.User;

public class UserDao extends BaseDao {

    public boolean add(User user){
        String sql="insert into schema.user(id,name,password,provinceid) values(?,?,?,?)";
        return executeSql(sql, user.getId(),user.getName(),user.getPassword(),user.getProvinceId());
    }

    public boolean update(User newUser){
        String sql="update schema.user set name=?,provinceid=?,password=? where id=?";
        return executeSql(sql, newUser.getName(),newUser.getProvinceId(),newUser.getPassword(),newUser.getId());
    }

    public boolean delete(User user){
        String sql="delete from schema.user where id=?";
        return executeSql(sql, user.getId());
    }

    public User getUserBySql(String sql,Object ...objects){
        Connection conn=getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            ps=conn.prepareStatement(sql);
            for(int i=0;i<objects.length;i++){
                ps.setString(i+1, objects[i].toString());
            }
            rs=ps.executeQuery();
            while(rs.next()){
                User user=new User();
                user.setId(rs.getString("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setProvinceId(rs.getString("provinceid"));
                return user;
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(rs, conn, null, ps);
        }
        return null;
    }

    public User getUserById(String id){
        String sql="select * from schema.user where id=?";
        return getUserBySql(sql, id);
    }

    public List<User> getUsers(){
        List<User> users=new ArrayList<>();
        Connection conn=getConn();
        PreparedStatement ps=null;
        ResultSet rs=null;
        String sql="select * from schema.user";
        try {
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                User user=new User();
                user.setId(rs.getString("id"));
                user.setName(rs.getString("name"));
                //不获取密码
                user.setProvinceId(rs.getString("provinceid"));
                users.add(user);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            closeAll(rs, conn, null, ps);
        }
        return users;
    }

    public static void main(String[] args) {
        // TODO Auto-generated method stub
        UserDao userDao=new UserDao();
        //User u=new User("1","郑","1111","1");
        //userDao.add(u);
        User user=userDao.getUserById("1");
        System.out.println(user);
    }

}