package org.wxd.weixin.util;
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;
public class MySQLUtil { public Connection getConnection(){ Connection conn = null; String url ="jdbc:mysql://IP地址/数据库"; String user ="用户名"; String password ="密码"; try { Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return conn; } /** * 释放资源 */ public void releaseResource(Connection conn, PreparedStatement ps,ResultSet rs){ try { if(null != rs) rs.close(); if(null != ps) ps.close(); if(null != conn) conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } //保存用户信息 public static void saveWeixinUser(String openId){ MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; String sql = "insert into weixin_user(open_id,subscribe_time,subscribe_status) values(?,now(),1)"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,null); } } //保存用户签到信息 public static void saveWeixinSign(String openId,int signPoints){ MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; String sql = "insert into weixin_sign(open_id,sign_time,sign_points) values(?,now(),?)"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.setInt(2, signPoints); ps.execute(); } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,null); } } //更新用户总积分 public static void updateUserPoints(String openId,int signPoints){ MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; String sql = "update weixin_user set points=points+? where open_id=?"; try { ps = conn.prepareStatement(sql); ps.setInt(1, signPoints); ps.setString(2, openId); ps.execute(); } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,null); } } //查询用户总积分 public static String selectUserPoints(String openId){ MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String sql = "select points from weixin_user where open_id=?"; String points = null; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); rs = ps.executeQuery(); if(rs.next()){ points = rs.getString("points"); } } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,rs); } return points; } //判断用户今天是否签到 public static boolean isTodaySigned(String openId){ boolean result = false; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String sql = "select count(*) as signCounts from weixin_sign where open_id=? and date_format(sign_time,'%Y-%m-%d')=date_format(now(),'%Y-%m-%d')"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); rs = ps.executeQuery(); int signCounts = 0; if(rs.next()){ signCounts = rs.getInt("signCounts"); } if(1 == signCounts) result = true; } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,rs); } return result; } /** * 判断用户本周是否第七次签到 * * @param openId 用户openid * @param monday 本周周一的日期时间 * @return */ public static boolean isSevenSign(String openId,String monday){ boolean result = false; MySQLUtil mysql = new MySQLUtil(); Connection conn = mysql.getConnection(); PreparedStatement ps = null; ResultSet rs = null; String sql = "select conut(*) as signCounts from weixin_sign where open_id=? and sign_time between str_to_date(?,'%Y-%m-%d %H:%i:%s') and now()"; try { ps = conn.prepareStatement(sql); ps.setString(1, openId); ps.setString(2, monday); rs = ps.executeQuery(); int signCounts = 0; if(rs.next()){ signCounts = rs.getInt("signCounts"); } if(6 == signCounts) result = true; } catch (SQLException e) { e.printStackTrace(); }finally { mysql.releaseResource(conn,ps,rs); } return result; }}
转载于:https://www.cnblogs.com/wenxudong/p/6232274.html
