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