myeclipse连接mysql数据库以及对数据库的增删改查

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_35219609/article/details/77922837

1、下载mysql的jdbc驱动包,将驱动包放入新建的文件夹中(点击下载


2、任意建一个test数据表


3、新建一个file文件后缀为.properties并添加相对应的数据


4、工具类DBConnectionUtil .java

package com.ljr.util;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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();
}
}



public static Connection getConnection(){
Connection conn=null;
try {
Class.forName(jdbcDriver);
try {
conn=DriverManager.getConnection(jdbcURL,jdbcUser,jdbcPassword);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
} catch (ClassNotFoundException 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();
}
}
}
}
}
public static void closeAll2(PreparedStatement st,Connection conn,ResultSet rs){

if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
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();
}
}
}
}
}
}
5、实体类User.java

package com.ljr.po;

public class User {

private int userId;
public int getUserId() {
return userId;
}
public void setUserId(int userId) {
this.userId = userId;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public int getSex() {
return sex;
}
public void setSex(int sex) {
this.sex = sex;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public int getFlag() {
return flag;
}
public void setFlag(int flag) {
this.flag = flag;
}
private String username;
private int sex;
private String password;
private int flag;
}
6、UserDao.java

package com.ljr.dao;

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

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

public class UserDao {

public UserDao(){

}



public boolean saveUser(User user){
boolean flag=false;
Connection conn=null;
PreparedStatement st=null;
try {
conn=DBConnectionUtil.getConnection();
String sql="insert into user(username,password) values(?,?)";
st =conn.prepareStatement(sql);
st.setString(1,user.getUsername());
st.setString(2,user.getPassword());
int rows=st.executeUpdate();
if(rows>0){
flag=true;
System.out.println(sql);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
System.out.println(".......");
DBConnectionUtil.closeAll(st, conn);
System.out.println("aaaaaaa");
}
return flag;
}



public boolean deleteUderByUserId(int userId){
boolean flag=false;
Connection conn=null;
PreparedStatement st=null;

try {
conn=DBConnectionUtil.getConnection();
String sql="delete from user where userId=?";
st=conn.prepareStatement(sql);
st.setInt(1, userId);
int rows=st.executeUpdate();
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;
PreparedStatement st=null;
try {
conn=DBConnectionUtil.getConnection();
String sql="update user set username=? where userId=?";
st=conn.prepareStatement(sql);
st.setString(1, (String)map.get("username"));
st.setString(2, (String)map.get("userId"));
int rows=st.executeUpdate();
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 pr=null;
ResultSet rs=null;
String sql="select * from user where userId=?";
try {
conn=DBConnectionUtil.getConnection();
pr=conn.prepareStatement(sql);
pr.setInt(1, userId);
rs=pr.executeQuery();
if(rs.next()){
user=new User();
user.setUserId(rs.getInt("userId"));
user.setUsername(rs.getString("username"));
user.setFlag(rs.getInt("flag"));
user.setPassword(rs.getString("password"));
user.setSex(rs.getInt("sex"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll2(pr,conn, rs);
}


return null;

}
public List<User> getAllUserList(){
List<User> userlist=new ArrayList<User>();
Connection conn=null;
PreparedStatement pr=null;
ResultSet rs=null;
String sql="select * from user";
try {
conn=DBConnectionUtil.getConnection();
pr=conn.prepareStatement(sql);
rs=pr.executeQuery();
while(rs.next()){
User user=new User();
user.setUserId(rs.getInt("userId"));
user.setUsername(rs.getString("username"));
user.setFlag(rs.getInt("flag"));
user.setPassword(rs.getString("password"));
user.setSex(rs.getInt("sex"));
userlist.add(user);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
DBConnectionUtil.closeAll2(pr,conn, rs);
}


return null;

}
}
7、测试类UserDaotest .java

package com.ljr.test;


import java.util.HashMap;
import java.util.Map;


import org.junit.Test;


import com.ljr.dao.UserDao;
import com.ljr.po.User;


public class UserDaotest {
UserDao userdao=new UserDao();
@Test
public void testSaveUser() throws Exception{
String username="哈哈mmm";
String password="11111";
User user=new User();
user.setUsername(username);
user.setPassword(password);
System.out.println(userdao.saveUser(user));
}
@Test
public void testSaveUser2() throws Exception{
String username="呜呜呜";
String password="11111";
User user=new User();
user.setUsername(username);
user.setPassword(password);
System.out.println(userdao.saveUser(user));
}
@Test
public void deleteUderByUserId() throws Exception{
System.out.println(userdao.deleteUderByUserId(4));
}
@Test
public void deleteUderByUserId2() throws Exception{
System.out.println(userdao.deleteUderByUserId(4));
}
@Test
public void updateUserByMap() throws Exception{
Map<String, Object> map=new HashMap<String, Object>();
map.put("username", "去去去");
    map.put("userId", "6");
    System.out.println(userdao.updateUserByMap(map));
}
@Test
public void updateUserByMap2() throws Exception{
Map<String, Object> map=new HashMap<String, Object>();
map.put("username", "去去去");
    map.put("userId", "6");
    System.out.println(userdao.updateUserByMap(map));
}
@Test
public void getUserByUserId() throws Exception{
UserDao userdao=new UserDao();
User user=userdao.getUserByUserId(10);
System.out.println(user.getUsername()+user.getUsername()+user.getFlag()+user.getPassword()+user.getSex());
}


}







阅读更多 登录后自动展开
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页