eclipse
package com.hz.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
* 数据库连接类
*
* @author ztw
*
* 2016-12-8
*/
public class DbHelp {
// com.microsoft.sqlserver.jdbc.SQLServerDriver.class
// MonsterChatRoom
private Connection conn = null;
private Statement stmt = null;
private PreparedStatement pstmt;
private static String dbDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
private static String dbUri = "jdbc:sqlserver://localhost:1433;dataBaseName=MonsterChatRoom;";
private static String dbUser = "sa";
private static String dbPwd = "123456";
public DbHelp() {
getConnection();
}
public Connection getConnection() {
try {
Class.forName(dbDriver);
conn = DriverManager.getConnection(dbUri, dbUser, dbPwd);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
if (conn == null) {
System.out.println("警告:数据库连接失败!");
} else {
System.out.println("数据库连接成功!");
}
return conn;
}
/**
* 获取statement(发送命令)对象
*
* @return
*/
public Statement getStatement() {
try {
if (stmt == null) {
stmt = conn.createStatement();
}
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
/**
* 预处理sql
*
* @param sql
* @return
*/
public PreparedStatement getPreparedStatement(String sql) {
try {
pstmt = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return pstmt;
}
/**
* 关闭数据库
*/
public void close() {
try {
if (pstmt != null) {
pstmt.close();
pstmt = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
dbDriver=Referenced Libraries->sqljdbc4.jar->……jdbc->最后以……Driver.class结尾的路径!
把libs文件中的jdbc4->右键导入路径Build Path
增删改查
package com.hz.util;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import com.hz.bean.User;
import com.hz.dao.DbOperate;
import com.hz.db.DbHelp;
/**
* 用户数据库连接类
*
* @author ztw
*
* 2016-12-7
*/
public class UserDatabaseUtil {
// 表名
private static final String T_USER = "T_User";
// 表中的列名
private static final String USER_ID = "user_id";
private static final String USER_NAME = "user_name";
private static final String USER_NICKNAME = "user_nickname";
private static final String USER_PASSWORD = "user_password";
private static final String USER_SEX = "user_sex";
private static final String USER_ICON = "user_icon";
private static final String USER_EMAIL = "user_email";
private static final String USER_MONEY = "user_money";
private static final String REGISTER_DATE = "register_date";
private static final String LOG_AUTOGRAPH = "log_autograph";
private DbHelp dbHelp;
private Statement statement;
private ResultSet rs;
User user;
public UserDatabaseUtil() {
dbHelp = new DbHelp();
statement = dbHelp.getStatement();
}
// 添加
public boolean add(User user) {
String sql = "insert into " + T_USER + "(" + USER_NAME + ","
+ USER_PASSWORD + "," + USER_NICKNAME + "," + USER_SEX + ","
+ USER_EMAIL + "," + LOG_AUTOGRAPH + ")"
+ " values(?,?,?,?,?,?)";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
/**
* 参数1:第几个问号 参数2:问号的值
*/
try {
pstmt.setString(1, user.getUser_name());
pstmt.setString(2, user.getUser_password());
pstmt.setString(3, user.getUser_nickname());
pstmt.setString(4, user.getUser_sex());
pstmt.setString(5, user.getUser_email());
pstmt.setString(6, user.getLog_autograph());
return pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
// 根据指定的信息查询数据
public User selectName(String user_name) {
try {
String sql = "select * from " + T_USER + " where " + USER_NAME
+ " = '" + user_name + "'";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
rs.getString(4), rs.getString(5), rs.getString(6),
rs.getString(7), rs.getDouble(8), rs.getString(9),
rs.getString(10));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 根据指定的信息查询数据
public User selectNickName(String NickName) {
try {
String sql = "select * from " + T_USER + " where " + USER_NICKNAME
+ " = '" + NickName + "'";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
rs.getString(4), rs.getString(5), rs.getString(6),
rs.getString(7), rs.getDouble(8), rs.getString(9),
rs.getString(10));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 根据指定的信息查询数据
public User selectName(User user_nickName) {
String nickName = user_nickName+"";
try {
String sql = "select * from " + T_USER + " where " + USER_NICKNAME
+ " = '" + nickName + "'";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
user = new User(rs.getInt(1), rs.getString(2), rs.getString(3),
rs.getString(4), rs.getString(5), rs.getString(6),
rs.getString(7), rs.getDouble(8), rs.getString(9),
rs.getString(10));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
// 修改信息
public void update(String user_nickname, String user_sex, String user_icon,
String user_email, String register_date, String log_autograph) {
String sql = "update " + T_USER + " set " + USER_NICKNAME + "='"
+ user_nickname + "' ," + USER_SEX + "='" + user_sex + "' ,"
+ USER_ICON + "='" + user_icon + "' ," + USER_EMAIL + "='"
+ user_email + "' ," + REGISTER_DATE + "='" + register_date
+ "' ," + LOG_AUTOGRAPH + "='" + log_autograph + "'"
+ " where " + USER_NICKNAME + " = '" + user_nickname + "'";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
try {
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 修改
public boolean update(String name,String pass) {
String sql = "update " + T_USER + " set " + USER_PASSWORD
+ "="+"'"+pass+"'"+" where " + USER_NAME + " = "+"'"+name+"'";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
try {
pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
// 删除
public boolean delete() {
// delete dbo.T_User where _id = 2
String sql = "delete " + T_USER + " where " + USER_ID + " = 2";
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
try {
return pstmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}
return false;
}
// 查找全部
public ResultSet getAll() {
try {
String sql = "select * from " + T_USER;
PreparedStatement pstmt = dbHelp.getPreparedStatement(sql);
rs = pstmt.executeQuery();
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
}
需要什么就调用相应的方法!