ql',now(),201501); Query OK, 1 row affected (0.00 sec) mysql> mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'zhangsan',now(),201502); Query OK, 1 row affected (0.00 sec) mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'lisi',now(),201503); Query OK, 1 row affected (0.01 sec) mysql> insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(1,1,'wangwu',now(),201504); Query OK, 1 row affected (0.00 sec) mysql> select * from msg where gid = 1 and create_month = 201501; +----+-----+---------+---------------------+--------------+ | id | gid | content | create_time | create_month | +----+-----+---------+---------------------+--------------+ | 1 | 1 | java | 2015-07-24 13:21:41 | 201501 | | 2 | 1 | oracle | 2015-07-24 13:21:41 | 201501 | +----+-----+---------+---------------------+--------------+ 2 rows in set (0.19 sec) mysql> select * from msg where gid = 1 and create_month = 201502; +----+-----+----------+---------------------+--------------+ | id | gid | content | create_time | create_month | +----+-----+----------+---------------------+--------------+ | 1 | 1 | zhangsan | 2015-07-24 13:21:42 | 201502 | +----+-----+----------+---------------------+--------------+ 1 row in set (0.00 sec) mysql> select * from msg where gid = 1 and create_month = 201503; +----+-----+---------+---------------------+--------------+ | id | gid | content | create_time | create_month | +----+-----+---------+---------------------+--------------+ | 1 | 1 | lisi | 2015-07-24 13:21:42 | 201503 | +----+-----+---------+---------------------+--------------+ 1 row in set (0.01 sec) mysql> select * from msg where gid = 1 and create_month = 201504; +----+-----+---------+---------------------+--------------+ | id | gid | content | create_time | create_month | +----+-----+---------+---------------------+--------------+ | 1 | 1 | wangwu | 2015-07-24 13:21:43 | 201504 | +----+-----+---------+---------------------+--------------+ 1 row in set (0.13 sec) mysql> select * from msg where gid = 2 and create_month = 201501; +----+-----+---------+---------------------+--------------+ | id | gid | content | create_time | create_month | +----+-----+---------+---------------------+--------------+ | 1 | 2 | ibm | 2015-07-24 13:21:41 | 201501 | | 2 | 2 | mysql | 2015-07-24 13:21:41 | 201501 | +----+-----+---------+---------------------+--------------+ 2 rows in set (0.01 sec)
4，java客户端调用测试
import java.sql.*; import java.sql.Date; import java.util.*; public class MycatTest { private static Connection connect = null; private static Statement statement = null; private static PreparedStatement preparedStatement = null; private static ResultSet resultSet = null; public static void init() { try { Class.forName("com.mysql.jdbc.Driver"); connect = DriverManager .getConnection("jdbc:mysql://192.168.100.1:8066/msg", "msg", "msg"); statement = connect.createStatement(); } catch (Exception e) { e.printStackTrace(); } } public static void close() { try { if (resultSet != null) { resultSet.close(); } } catch (Exception e) { } try { if (statement != null) { statement.close(); } } catch (Exception e) { } try { if (connect != null) { connect.close(); } } catch (Exception e) { } } public static void testInsert() { //实际当中i为gid的自增id。跨按月分区自增。 for (int i = 1; i < 100; i++) { try { //特意设置28循环周期。 int j = (i / 28) + 1; preparedStatement = connect .prepareStatement("insert into msg(`id`,`gid`,`content`,`create_time`,`create_month`) values(?,?,?,?,?)"); //录入参数。 preparedStatement.setInt(1, i); preparedStatement.setInt(2, 99); preparedStatement.setString(3, "test content " + i); //插入j时间 preparedStatement.setDate(4, new java.sql.Date(2015, j - 1, i)); //设置按月分区。 preparedStatement.setInt(5, 201500 + j); preparedStatement.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } } } static class Msg { private int id; private int gid; private String content; private java.util.Date createTime; private int createMonth; public int getId() { return id; } public void setId(int id) { this.id = id; } public int getGid() { return gid; } public void setGid(int gid) { this.gid = gid; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public java.util.Date getCreateTime() { return createTime; } public void setCreateTime(java.util.Date createTime) { this.createTime = createTime; } public int getCreateMonth() { return createMonth; } public void setCreateMonth(int createMonth) { this.createMonth = createMonth; } @Override public String toString() { return "Msg{" + "id=" + id + ", gid=" + gid + ", content='" + content + '\'' + ", createTime=" + createTime + ", createMonth=" + createMonth + '}'; } } public static List
selectByGidMonth(int gid, int month, int id, int limit) { List list = new ArrayList
(); |