H2使用备忘

 

1. 自增ID

 

id int identity(1,1)

 

2. 建表语句

 

create table manage_item(
    id int identity(1,1),
    name varchar(10),
    url varchar(100),
    target varchar(20),
    winOpt varchar(500)
);

 

3. 插入语句

 

insert into manage_item(name,url,target,winOpt) values('部门设置','mdgxx.jsp','bqt','height=380, width=650, top=100, left=50, toolbar=yes, menubar=no, scrollbars=yes, resizable=yes,location=no, status=no');

 

4. H2 修改列类型

 

alter table tablename altertype columnname columntype

 

 

4. 常用操作的代码

 

package com.tntxia.game.rpg.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.ResourceBundle;

import org.h2.tools.Server;

public class DBManager {
	
	private static String dir;
	
	private static String user;
	
	private static String password;
	
	private static String serverPort;
	
	private static Server server;
	
	static{
		ResourceBundle rs = ResourceBundle.getBundle("jdbc");
		dir = rs.getString("dir");
		user = rs.getString("user");
		password = rs.getString("password");
		serverPort = rs.getString("port");
		try {
			Class.forName("org.h2.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
	}
	
	public static boolean isServerRunning(){
		return server!=null && server.isRunning(false);
	}
	
	public static void startServer() {
		try {
			System.out.println("正在启动h2...");
			server = Server.createTcpServer(
					new String[] { "-tcpPort", serverPort }).start();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public void stopServer() {
		if (server != null) {
			System.out.println("正在关闭h2...");
			server.stop();
			System.out.println("关闭成功.");
		}
	}
	
	public static Connection getConn(){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection("jdbc:h2:tcp://localhost:9094/" + dir, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	public static ResultSet query(String sql,Connection conn){
		
		ResultSet rs = null;
		try {
			Statement stat = conn.createStatement();
			rs = stat.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public static ResultSet query(String sql,Connection conn,List<Object> args){
		
		ResultSet rs = null;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0;i<args.size();i++){
				Object arg = args.get(i);
				if(arg instanceof String){
					ps.setString(i+1, (String)arg);
				}else if(arg instanceof Integer){
					ps.setInt(i+1, (Integer)arg);
				}else{
					ps.setString(i+1, (String)arg);
				}
			}
			rs = ps.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return rs;
	}
	
	public static void update(String sql){
		Connection conn = getConn();
		try {
			Statement stat = conn.createStatement();
			stat.executeUpdate(sql);
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static void update(String sql,List<Object> args){
		Connection conn = getConn();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			for(int i=0;i<args.size();i++){
				Object arg = args.get(i);
				if(arg instanceof String){
					ps.setString(i+1, (String)arg);
				}else if(arg instanceof Integer){
					ps.setInt(i+1, (Integer)arg);
				}else{
					ps.setString(i+1, (String)arg);
				}
			}
			ps.executeUpdate();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static ResultSet execute(String sql){
		Connection conn = getConn();
		ResultSet rs = null;
		try {
			Statement stat = conn.createStatement();
			boolean r = stat.execute(sql);
			if(r){
				rs = stat.getResultSet();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	public static void main(String args[]){
		DBManager.startServer();
	}
	
}

 

5. 当前时间 

 

   H2 获取当前时间的方法：curtime()