mysql连接数据库java_MySql连接数据库和操作(java)

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;

}

}