主要分为四步
建一个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();
}
}
效果如下图所示
package com.log.test;
import com.log.dao.UserDao;
import com.log.dao.impl.UserDaoImpl;
public class TestLogin {
/**
* 测试登录
*/
public static void main(String[] args) {
String name = "zhangsan";
String pwd = "123";
UserDao userDao = new UserDaoImpl();
boolean flag = userDao.getUserByUserPwd(name, pwd);
if (flag) {
flag = true;
System.out.println("登录成功");
} else {
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("删除失败");
}
}
}
数据图如下图所示
