public class JDBCUtils {
private static final String TAG = "mysql-party-JDBCUtils";
private static String driver = "com.mysql.jdbc.Driver";// MySql驱
private static String dbName = "party";// 数据库名称
private static String user = "root";// 用户名
private static String password = "123456";// 密码
public static Connection getConn(){
Connection connection = null;
try{
Class.forName(driver);// 动态加载类
String ip = "10.99.113.121";// 写成本机地址，不能写成localhost，同时手机和电脑连接的网络必须是同一个
// 尝试建立到给定数据库URL的连接
connection = DriverManager.getConnection("jdbc:mysql://" + ip + ":3306/" + dbName,
user, password);
}catch (Exception e){
e.printStackTrace();
}
return connection;
}
}
public class UserDao {
private static final String TAG = "mysql-party-UserDao";
/**
* function: 登录
*/
public static boolean login(String userAccount, String userPassword) {
Connection connection = null;
boolean success = false;
try {
connection = JDBCUtils.getConn();
if (connection != null) {
String query = "SELECT * FROM user WHERE userAccount = ? AND userPassword = ?";
try (PreparedStatement pstmt = connection.prepareStatement(query)) {
pstmt.setString(1, userAccount);
pstmt.setString(2, userPassword);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
Log.d("MysqlHelp_denglu", "用户名和密码匹配成功！");
success = true;
} else {
Log.d("MysqlHelp_denglu", "用户名和密码不匹配。");
success = false;
}
}
}
} else {
Log.d("MysqlHelp_denglu", "无法获取数据库连接。");
}
} catch (Exception ex) {
Log.e("MysqlHelp_denglu", "验证登录时发生异常", ex);
success = false;
}
return success;
}
/**
* function: 注册
*/
public static boolean register(User user) {
// 根据数据库名称，建立连接
Connection connection = JDBCUtils.getConn();
try {
String sql = "insert into user(userAccount,userName,userPhone,userClass,userPassword) values (?,?,?,?,?)";
if (connection != null) {// connection不为null表示与数据库建立了连接
PreparedStatement ps = connection.prepareStatement(sql);
if (ps != null) {
//将数据插入数据库
ps.setString(1, user.getUserAccount());
ps.setString(2, user.getUserName());
ps.setString(3, user.getUserPhone());
ps.setString(4, user.getUserClass());
ps.setString(5, Integer.toString(123456));
// 执行sql查询语句并返回结果集
int rs = ps.executeUpdate();
if (rs > 0)
return true;
else
return false;
} else {
return false;
}
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
Log.e(TAG, "异常register：" + e.getMessage());
return false;
}
}
/**
* function: 打卡
*/
// 在方法中获取当前系统日期并插入数据库
public static Boolean daka(String time_s, String time_e, String text, String account) {
Connection connection = JDBCUtils.getConn();
try {
// 检查数据库是否已经存在相同日期的记录
String checkSql = "SELECT COUNT(*) FROM infor WHERE date = ?";
PreparedStatement checkPs = connection.prepareStatement(checkSql);
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String currentDate = dateFormat.format(new Date());
checkPs.setString(1, currentDate);
ResultSet resultSet = checkPs.executeQuery();
if (resultSet.next() && resultSet.getInt(1) > 0) {
}
// 插入新打卡记录
String insertSql = "INSERT INTO infor(time_s, time_e, text, account, date) VALUES (?, ?, ?, ?, ?)";
PreparedStatement ps = connection.prepareStatement(insertSql);
ps.setString(1, time_s);
ps.setString(2, time_e);
ps.setString(3, text);
ps.setString(4, account);
ps.setString(5, currentDate);
int rs = ps.executeUpdate();
return rs > 0;
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
// 关闭资源
try {
if (connection != null) {
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
/**
* function: 打卡记录
*/
public static String records(String account) throws SQLException {
Connection connection = null;
PreparedStatement statement = null;
ResultSet resultSet = null;
StringBuilder resultBuilder = new StringBuilder();
try {
connection = JDBCUtils.getConn();
String sql = "SELECT * FROM infor WHERE Account = ?";
statement = connection.prepareStatement(sql);
statement.setString(1, account);
resultSet = statement.executeQuery();
while (resultSet.next()) {
String date = resultSet.getString("date");
String resultAccount = resultSet.getString("Account");
String times = resultSet.getString("time_s");
String timee = resultSet.getString("time_e");
String text = resultSet.getString("text");
// 将查询结果拼接为字符串
resultBuilder.append("日期：").append(date).append("\n").append("学号: ").append(resultAccount).append("\n")
.append("开始时间: ").append(times).append("\n")
.append("结束时间: ").append(timee).append("\n")
.append("学习内容: ").append(text).append("\n\n");
}
} catch (SQLException e) {
e.printStackTrace();
}
return resultBuilder.toString();
}
/**
* function: 设置学习目标
*/
public static boolean tar(User user, String account) {
// 根据数据库名称，建立连接
Connection connection = JDBCUtils.getConn();
try {
String sql = "insert into goal(date1, account, target) values (?, ?, ?)";
if (connection != null) { // connection 不为null表示与数据库建立了连接
PreparedStatement ps = connection.prepareStatement(sql);
if (ps != null) {
// 获取当前系统日期并格式化为字符串
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String currentDate = dateFormat.format(new Date());
// 插入当前系统日期作为第五个数据
ps.setString(1, currentDate);
ps.setString(2, account);
ps.setString(3, user.getTarget());
// 执行sql查询语句并返回结果集
int rs = ps.executeUpdate();
if (rs > 0)
return true;
else
return false;
} else {
return false;
}
} else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
/**
* function: 完成度分析
*/
public static boolean ana(User user, String account) {
Connection connection = JDBCUtils.getConn();
// 获取当前系统日期
Calendar calendar = Calendar.getInstance();
// 往前推四天
calendar.add(Calendar.DAY_OF_MONTH, -4);
// 格式化日期为 "yyyy-MM-dd" 格式的字符串
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String formattedDate = dateFormat.format(calendar.getTime());
try {
// 构建更新语句，更新符合账户和前四天日期的 analysis 数据
String updateSql = "UPDATE goal SET analysis = ?, buzu = ? WHERE date1 = ? AND account = ?";
PreparedStatement updatePs = connection.prepareStatement(updateSql);
updatePs.setString(1, user.getAnalysis());
updatePs.setString(2, user.getBuzu());
updatePs.setString(3, formattedDate);
updatePs.setString(4, account);
int result = updatePs.executeUpdate(); // 执行更新操作
return result > 0; // 返回更新是否成功的结果
} catch (SQLException e) {
e.printStackTrace();
return false; // 更新过程中出现异常，返回更新失败
}
}
/**
* function: 统计打卡次数
*/
浙公网安备 33010602011771号