java连接my sql数据库以及增删改查方法的实现

1，新建java项目（或者web项目，这里以java为例），以user表为例子

2，连接数据库，新建jdbc,properties（数据库名，用户名，密码以自己的来写）

3，这里我将数据库的连接代码封装在DBConnectionUtil.java里面

package com.beiruan.util;


import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class DBConnectionUtil {
//四个参数：驱动，地址，用户名，密码
private static String jdbcDriver="";
private static String jdbcURL="";
private static String jdbcUser="";
private static String jdbcPassword="";

static {
InputStream is=DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties p=new Properties();
try {
p.load(is);
jdbcDriver=p.getProperty("jdbcDriver");
jdbcURL=p.getProperty("jdbcURL");
jdbcUser=p.getProperty("jdbcUser");
jdbcPassword=p.getProperty("jdbcPassword");
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


//需要设置为static
public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(jdbcDriver);
conn=DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

return conn;
}


public static void closeAll(Statement st,Connection conn){
//先产生的后关闭，后产生的先关闭
if(st!=null)
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(conn!=null)
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


}



}

4，基本的增删改查，还是以user表为例，user类读者自行解决，user属性有以下几个。这里方法都放在dao里面

package com.beiruan.dao;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;


import com.beiruan.po.User;
import com.beiruan.util.DBConnectionUtil;


public class UserDao {

//增加用户
public boolean saveUser(User user){
boolean flag=false;
Statement st=null;
Connection conn=null;
try {
conn=DBConnectionUtil.getConnection();
String sql="insert into user (username,password) values ('"+user.getUsername()+"','"+user.getPassword()+"')";
st=conn.createStatement();
int rows=st.executeUpdate(sql);
if(rows>0)
flag=true;
System.out.println(sql);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll(st, conn);
}
return flag;
}

//删除用户
public boolean deleteUserByUserId(int userId){
Connection conn=null;
Statement st=null;
boolean flag=false;
try {
conn=DBConnectionUtil.getConnection();
String sql="delete from user where userId="+userId;
st=conn.createStatement();
int rows=st.executeUpdate(sql);
if(rows>0)
flag=true;

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll(st, conn);
}
return flag;

}
//修改用户
public boolean updateUserByMap(Map<String, Object> map){
boolean flag=false;
Connection conn=null;
Statement st=null;
try {
conn=DBConnectionUtil.getConnection();
st=conn.createStatement();
String sql="update user set password='"+(String)map.get("password")+"' where userId="+(int)map.get("userId");
int rows=st.executeUpdate(sql);
if(rows>0)
flag=true;
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll(st, conn);
}

return flag;
}


}

//改进查找方法，注意这里用的？代替参数，这样写更简单
public User getUserByUserId(int userId){
User user=null;
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
conn=DBConnectionUtil.getConnection();
String sql="select * from user where userId =?";
try {
ps=conn.prepareStatement(sql);
ps.setInt(1, userId);
rs=ps.executeQuery();
//游标,返回boolean类型
if(rs.next()){
user =new User();
user.setUserId(rs.getInt("userId"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setFlag(rs.getInt("flag"));
user.setSex(rs.getInt("sex"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll(rs,ps, conn);
}

return user;
}