mysql分表操作--简单设计

 

这里做个例子进行分表，设计很简单，根据类型不同进行分表，比如有三种类型，三张表结构一模一样，建立一个表名索引表，然后根据类型在这张索引表里查询要插入的表名，然后再进行插入操作。

 

索引表：

CREATE TABLE tableindex (

  id int(11) NOT NULL AUTO_INCREMENT,

  tablename varchar(10) DEFAULT NULL,

  type varchar(10) DEFAULT NULL,

  PRIMARY KEY (id)

) 

 

业务表：

CREATE TABLE `e` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `password` varchar(20) DEFAULT NULL,

  `name` varchar(20) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 

三张表e,e0,e1表结构一样

 

package com.msg.domain;

public class User implements java.io.Serializable {
	private static final long serialVersionUID = 1L;
	private String name;
	private String password;
	private String type;
	public User() {
		
	}
	
	public User(String name, String password) {
		this.name = name;
		this.password = password;
	}
	
	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}
	
}

 

 

 

public void insertMessage() {
		JdbcUtils jdbc = new JdbcUtils();//JDBC操作类
		try {
			List<User> userList = new ArrayList<User>();
			User user0 = new User(); 
			user0.setName("张三");
			user0.setPassword("123");
			user0.setType("1");
			userList.add(user0);
			User user1 = new User(); 
			user1.setName("李四");
			user1.setPassword("123456");
			user1.setType("2");
			userList.add(user1);
			User user2 = new User(); 
			user2.setName("王五");
			user2.setPassword("890");
			user2.setType("3");
			userList.add(user2);
			for (User user:userList){
				StringBuffer sb = new StringBuffer();
				//查询业务表名
				String tableName = 
					jdbc.query("select * from tableindex where type='"+user.getType()+"'");
				sb.append("insert into "+tableName+"(name,password) values('"+user.getName()+"','"+
						user.getPassword()+"')");
				//执行插入操作
				jdbc.insert(sb.toString());
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}