jdbc连接Sql Sever 2008对数据进行基本的增删改查操作

KdanMin

已于 2022-11-11 11:39:27 修改

阅读量3.5k

点赞数 2

文章标签： java

于 2016-08-10 11:13:20 首次发布

本文链接：https://blog.csdn.net/qq_15950325/article/details/52169462

版权

主要分为四步

建一个javabean实体类

package com.log.common;


import java.util.Date;


public class UserInfo {
private int U_id;
private String U_Name;


public UserInfo(String u_Name, String u_Pwd, String phone) {
super();
U_Name = u_Name;
U_Pwd = u_Pwd;
this.phone = phone;
}


private String U_Pwd;
//private String RE_Pwd；
private String phone;
private String U_Birthday;
private int U_status;
private Date U_createTime;
public UserInfo(String u_Name, String u_Pwd,  String u_Birthday,String phone) {
super();
U_Name = u_Name;
U_Pwd = u_Pwd;
U_Birthday = u_Birthday;
this.phone = phone;
}


public UserInfo(int  u_id,String u_Name, String u_Pwd, String phone) {
super();
u_id=u_id;
U_Name = u_Name;
U_Pwd = u_Pwd;
this.phone = phone;
}


public UserInfo(String u_Name, String u_Pwd) {
super();
U_Name = u_Name;
U_Pwd = u_Pwd;
}



public String getPhone() {
return phone;
}


public void setPhone(String phone) {
this.phone = phone;
}


public UserInfo() {
super();
// TODO Auto-generated constructor stub
}


public UserInfo(int u_id, String u_Name, String u_Pwd, String u_Birthday,
int u_status, Date u_createTime) {
super();
U_id = u_id;
U_Name = u_Name;
U_Pwd = u_Pwd;
U_Birthday = u_Birthday;
U_status = u_status;
U_createTime = u_createTime;
}


public int getU_id() {
return U_id;
}


public void setU_id(int u_id) {
U_id = u_id;
}


public String getU_Name() {
return U_Name;
}


public void setU_Name(String u_Name) {
U_Name = u_Name;
}


public String getU_Pwd() {
return U_Pwd;
}


public void setU_Pwd(String u_Pwd) {
U_Pwd = u_Pwd;
}


public String getU_Birthday() {
return U_Birthday;
}


public void setU_Birthday(String u_Birthday) {
U_Birthday = u_Birthday;
}


public int getU_status() {
return U_status;
}


public void setU_status(int u_status) {
U_status = u_status;
}


public Date getU_createTime() {
return U_createTime;
}


public void setU_createTime(Date u_createTime) {
U_createTime = u_createTime;
}


@Override
public String toString() {
return "UserInfo [U_id=" + U_id + ", U_Name=" + U_Name + ", U_Pwd="
+ U_Pwd + ", U_Birthday=" + U_Birthday + ", U_status="
+ U_status + ", U_createTime=" + U_createTime + "]";
}


}

第二步新建一个工具类DBUtil类主要放数据信息包括url，username,password等

DBUtil

package com.log.util;


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.Date;


import com.log.common.UserInfo;


public class DBUtil {
private static final String DRIVE_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static final String URL = "jdbc:sqlserver://localhost:1433;databaseName=logInfo";
private static final String USER_NAME = "sa";
private static final String PWD = "921122";
public static Connection testconn() throws SQLException {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps=null;


try {
System.out.println("驱动加载成功!");
Class.forName(DRIVE_NAME);
conn = DriverManager.getConnection(URL, USER_NAME, PWD);
if (conn != null) {
System.out.println("数据库连接成功！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
System.out.println("连接失败!");
e.printStackTrace();
DBUtil.closeAll(conn, stmt, rs);
// conn.close();
}
return conn;

}
//释放资源
public static void closeAll(Connection conn, Statement stmt, ResultSet rs) {
try {
if(rs!=null){
rs.close();
}
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

第二步新建一个UserDao接口,里面查询用户列表，添加用户信息，根据用户名跟密码验证用户信息，更新用户信息，根据id删除用户信息，根据id查询用户信息等。

public interface UserDao {
//获取用户信息
List<UserInfo> getAllUser(UserInfo userInfo);
//获取用户列表
    List<UserInfo> getAllUserList();
    //插入用户信息
boolean InsertInto(UserInfo userInfo);


// 根据用户名跟密码查询
boolean getUserByUserPwd(String username, String pwd);
     //更新用户信息
boolean UpdateUser(UserInfo user);
//根据id删除信息
boolean deleteUser(int id);
//根据id查询用户信息
UserInfo UserInfoById(int id);


}

第三步建立接口的实现类UserInfoImpl

package com.log.dao.impl;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import com.log.common.UserInfo;
import com.log.dao.UserDao;
import com.log.util.DBClose;
import com.log.util.DBUtil;
import com.log.util.timeUtil;


public class UserDaoImpl implements UserDao {
private DBUtil db;


@Override
public List<UserInfo> getAllUser(UserInfo userInfo) {
// 返回用户信息列表
List<UserInfo> list = new ArrayList<UserInfo>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
PreparedStatement ps = null;
try {
// 获取连接
conn=db.testconn();
System.out.println("连接成功");
stmt = conn.createStatement();
String sql = "select * from userInfo";
rs = stmt.executeQuery(sql);
while (rs.next()) {
userInfo = new UserInfo();
int uid = rs.getInt(userInfo.getU_id());
String username = rs.getString(userInfo.getU_Name());
String pwd = rs.getString(userInfo.getU_Pwd());
// String str="";
// SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
// str=sdf.format(userInfo.getU_Birthday());
// String bir=rs.getLong(userInfo.getU_Birthday());
userInfo.setU_id(uid);
userInfo.setU_Name(username);
userInfo.setU_Pwd(pwd);
list.add(userInfo);
}
// Date date=rs.getDate(userInfo.getU_Birthday());
// if(date!=null){
// timeUtil.timeToStr(date);
// }


} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
db.closeAll(conn, stmt, rs);
}


return list;
}


@Override
public boolean InsertInto(UserInfo userInfo) {
boolean flag = false;// 标签记录插入结果
Connection conn = null;
Statement stmt = null;
PreparedStatement pstmt = null;
ResultSet rs=null;
try {
conn=db.testconn();// 获取连接
String sql = "insert into userInfo(U_Name,U_Pwd,U_Birthday,U_Phone)values(?,?,?,?);";
     pstmt = conn.prepareStatement(sql);// 加载sql
pstmt.setString(1, userInfo.getU_Name());// 为占位符赋值
pstmt.setString(2, userInfo.getU_Pwd());
 String str = null;
 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
 str = sdf.format(userInfo.getU_Birthday());
 // if (date != null) {
 // str = timeUtil.timeToStr(date);// 时间格式转字符串
 // }
pstmt.setString(3, userInfo.getU_Birthday());// 为占位符赋值
pstmt.setString(4, userInfo.getPhone());
int rowN = pstmt.executeUpdate();// 执行sql
if (rowN == 1) {// 执行成功时
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
       
// 清理资源
db.closeAll(conn, stmt, rs);
}
return flag;
}


@Override
public boolean getUserByUserPwd(String username, String pwd) {
boolean flag = false;// 创建userInfo
Connection conn = null;
PreparedStatement pstmt = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn=db.testconn();
//select * from userInfo where U_Name='"+username+"'and U_Pwd='"+pwd+"'
//
// 
String sql = "select * from userInfo where U_Name=? AND U_Pwd=?";
// stmt=conn.createStatement();
pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, pwd);
rs = pstmt.executeQuery();
if (rs.next()) {// 将查询信息封装到userInfo对象中
flag = true;
}
} catch (Exception e) {
System.out.println("数据库访问异常");
} finally {
// 清理资源
    db.closeAll(conn, stmt, rs);
}
return flag;
}


@Override
public boolean UpdateUser(UserInfo user) {
boolean flag = false;
Connection conn = null;
Statement stmt = null;
PreparedStatement ps = null;
//ResultSet rs=null;
try {
conn=db.testconn();
// update userInfo set U_Name=?,U_Pwd=?,U_Birthday=?,U_Phone where
// U_ID=?
String sql = "update userInfo set U_Name=?,U_Pwd=?,U_Birthday=?,U_Phone=? where U_ID=?";
// /stmt=conn.createStatement();


ps = conn.prepareStatement(sql);
ps.setString(1, user.getU_Name());
ps.setString(2, user.getU_Pwd());
// String str = "";
// SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
// str = sdf.format(user.getU_Birthday());
ps.setString(3, user.getU_Birthday());
ps.setString(4, user.getPhone());
ps.setInt(5, user.getU_id());
int rowNum = ps.executeUpdate();
if (rowNum == 1) {
flag = true;
}


} catch (SQLException e) {
System.out.println("更新失败" + e.getErrorCode());
} finally {
// 清理资源
db.closeAll(conn, stmt, null);
}
return flag;
}


@Override
public boolean deleteUser(int id) {
boolean flag = false;
Connection conn = null;
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs=null;
try {
conn=db.testconn();
stmt = conn.createStatement();
String sql = "delete from userInfo where U_ID=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
int rowN = ps.executeUpdate();
// flag=stmt.execute(sql);
if (rowN == 1) {
flag = true;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 清理资源
db.closeAll(conn, stmt, rs);
}
return flag;
}


@Override
public List<UserInfo> getAllUserList() {
// TODO Auto-generated method stub
List<UserInfo> list = new ArrayList<UserInfo>();
Connection conn = null;
PreparedStatement ps = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn=db.testconn();
String sql="select * from userInfo";
stmt=conn.createStatement();
//ps = conn.prepareStatement("");
// while(rs.next()){
rs=stmt.executeQuery(sql);
while (rs.next()) {
 UserInfo userInfo=new UserInfo();
 int id=rs.getInt("U_ID");
 String name=rs.getString("U_Name");
 String pwd=rs.getString("U_Pwd");
 userInfo.setU_id(id);
 userInfo.setU_Name(name);
 userInfo.setU_Pwd(pwd);
list.add(userInfo);
}
} catch (SQLException e) {
System.out.println("数据访问异常");
} finally {
db.closeAll(conn, stmt, rs);
}


return list;
}


@Override
public UserInfo UserInfoById(int id) {
// 根据id查询信息
UserInfo userInfo = new UserInfo();
PreparedStatement ps = null;
Connection conn = null;
ResultSet rs = null;
try {
conn=db.testconn();
String sql = "select * from userInfo where U_ID=?";
ps = conn.prepareStatement(sql);
ps.setInt(1, id);
rs = ps.executeQuery();
if (rs.next()) {
String UName = rs.getString("U_Name");
String UPwd = rs.getString("U_Pwd");
String Ubir = rs.getString("U_Name");
String UPhone = rs.getString("U_Phone");
int UID = rs.getInt("U_ID");
userInfo.setU_id(UID);
userInfo.setU_Name(UName);
userInfo.setU_Pwd(UPwd);
userInfo.setU_Birthday(Ubir);
userInfo.setPhone(UPhone);


}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return userInfo;
}
}

第四步建立Test类

package com.log.test;


import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import com.log.common.RegUserInfo;
import com.log.common.UserInfo;
import com.log.dao.RegUserInfoDao;
import com.log.dao.UserDao;
import com.log.dao.impl.RegUserInfoImpl;
import com.log.dao.impl.UserDaoImpl;
import com.log.util.DBUtil;


public class DBTest {

        //添加用户信息

public static void main(String[] args) throws SQLException {
UserDao userInfoDao=new UserDaoImpl();
UserInfo userInfo=new UserInfo("jaks", "123","2009-08-07","8976566");
//userInfo.setU_id(4);

// userInfo.setU_Name("adjhsjs");
// userInfo.setU_Pwd("123444");
// userInfo.setPhone("18388383");
// String bir="2050-08-08";
// String date=null;
// SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
// try {
// date=sdf.parse(bir);
// } catch (ParseException e) {
// // TODO Auto-generated catch block
// System.out.println("时间格式转换失败！");
// }

boolean flag=userInfoDao.InsertInto(userInfo);
//boolean flag=userInfoDao.deleteUser(10);


// boolean flag=userInfoDao.UpdateUser(userInfo);

if(flag){
//System.out.println("成功更新一条数据");
//System.out.println("成功插入一条记录");
System.out.println("插入成功");
}else{
System.out.println("插入失败");

｝

数据库效果图如下

}

TestUserList

package com.log.test;


import java.util.ArrayList;
import java.util.List;


import com.log.common.UserInfo;
import com.log.dao.UserDao;
import com.log.dao.impl.UserDaoImpl;


public class TestUserList {
public static void main(String[] args) {
//获取用户列表
UserDao userDao=new UserDaoImpl();
List<UserInfo> list=userDao.getAllUserList();
for (int i = 0; i < list.size(); i++) {
String name=list.get(i).getU_Name();
String pwd=list.get(i).getU_Pwd();
String birthday=list.get(i).getU_Birthday();
String phone=list.get(i).getPhone();
System.out.print("\tname:"+name);
System.out.print("\t\t\npwd:"+pwd);
System.out.print("\tbirthday:"+birthday);
System.out.print("\t\t\nphone:"+phone);

}
System.out.println();

}


}

效果如下图所示

TestUserList

package com.log.test;


import java.util.ArrayList;
import java.util.List;


import com.log.common.UserInfo;
import com.log.dao.UserDao;
import com.log.dao.impl.UserDaoImpl;


public class TestUserList {
public static void main(String[] args) {
//获取用户列表
UserDao userDao=new UserDaoImpl();
List<UserInfo> list=userDao.getAllUserList();
for (int i = 0; i < list.size(); i++) {
String name=list.get(i).getU_Name();
String pwd=list.get(i).getU_Pwd();
String birthday=list.get(i).getU_Birthday();
String phone=list.get(i).getPhone();
System.out.print("\tname:"+name);
System.out.print("\t\t\npwd:"+pwd);
System.out.print("\tbirthday:"+birthday);
System.out.print("\t\t\nphone:"+phone);

}
System.out.println();

}


}

}

测试连接类TestConn

package com.log.test;


import java.sql.SQLException;


import com.log.util.DBUtil;


public class TestConn {
/**
 * 测试连接
 * @throws SQLException 
 *
 */
public static void main(String[] args) throws SQLException {
DBUtil.testconn();


}

}

TestDelete

package com.log.test;


import com.log.dao.UserDao;
import com.log.dao.impl.UserDaoImpl;


public class TestDelete {
public static void main(String[] args) {
UserDao userDao=new UserDaoImpl();
boolean flag=userDao.deleteUser(17);
if(flag){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
}