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()