H2内存数据库

准备素材 ： H2 数据库 jar包（ 导入到web工程lib下 ）

java代码

三种模式

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


import javax.annotation.Resource;


import com.STNCP.Tracker.Service.AuthorizeCodeService;




public class TestH2 {

@Resource
private static AuthorizeCodeService acService;

//Embedded模式
public static void h1(){

try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e2) {
// TODO Auto-generated catch block
e2.printStackTrace();
}
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
conn = DriverManager.
getConnection("jdbc:h2:E:\\research\\workspace\\H2Test\\db\\test", "sa", "");
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}

try {
stmt = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
/* try {
stmt.execute("INSERT INTO lbssql VALUES ('3123', '3213', '3213', '3213', '3213', '3213', '3213')");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}*/
 
try {
rs = stmt.executeQuery("SELECT * FROM AuthorizeCode");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}   
     try {
while(rs.next()) {   
System.out.println(rs.getString("Token"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//server模式
public static void h2(){
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  Connection conn = null;
try {
conn = DriverManager.
getConnection("jdbc:h2:~/test", "sa", ""); 
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  Statement stmt = null;
try {
System.out.println(conn);
stmt = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  ResultSet rs = null;
try {

stmt.execute("INSERT INTO lbssql VALUES ('3123', 'liuyutong', '3213', '3213', '3213', '3213', '3213')");
rs = stmt.executeQuery("SELECT * FROM lbssql ");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}   
   try {
while(rs.next()) {   
System.out.println("成功MNC==>"+rs.getString("MNC"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//内存模式
public static void h3(){
 try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  Connection conn = null;
try {
conn = DriverManager.
      getConnection("jdbc:h2:mem:test", "sa", "");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
  // add application code here
  Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
 
try {
//stmt.executeUpdate("CREATE TABLE TEST_MEM(ID INT PRIMARY KEY,NAME VARCHAR(255));");
 
stmt.executeUpdate("DROP TABLE IF EXISTS token");
stmt.executeUpdate("CREATE TABLE token ( "
+ "UserID bigint(12) NOT NULL, Token varchar(50) NOT NULL"
+ ",StartTime datetime NOT NULL,EndTime datetime NOT NULL,Created datetime NOT NULL ,"
+ "CreateBy bigint(12) NOT NULL,Updated datetime NOT NULL,UpdateBy bigint(12) NOT NULL  "
+ ") ENGINE=InnoDB DEFAULT CHARSET=utf8;");
 
stmt.executeUpdate("INSERT INTO token VALUES (1, '219263189', '2015-7-22 10:43:52', '2015-7-22 11:13:52', '2015-7-15 20:53:52', 1, '2015-7-15 20:53:52', 1)");


 

 

} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

  ResultSet rs = null;
try {
rs = stmt.executeQuery("SELECT * FROM token");
} catch (SQLException e) { 
// TODO Auto-generated catch block
e.printStackTrace();
}   
   try {
while(rs.next()) {   
try {
System.out.println(rs.getString("Token"));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
  try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}


public static void main(String[] args)throws Exception {
//TestH2.h1();
//TestH2.h2();
 TestH2.h3();
 


}

}

h2导入导出数据

export：

call CSVWRITE ( 'C:/MyFolder/MyCSV.txt', 'SELECT * FROM MYTABLE' )

import：

insertinto MYTABLE ( select * from csvread('d:/output1.csv')) ; 

Mysql 导出csv文件：

select * fromtest_info   
into outfile 'd://output1.csv'   
fields terminated by ',' optionally enclosed by '"' escaped by'"'   
lines terminated by '\r\n'; 

里面最关键的部分就是格式参数

这个参数是根据RFC4180文档设置的，该文档全称Common Format and MIME Type for Comma-Separated Values (CSV) Files，其中详细描述了CSV格式，其要点包括：
(1)字段之间以逗号分隔，数据行之间以\r\n分隔；

(2)字符串以半角双引号包围，字符串本身的双引号用两个双引号表示。

通过执行上述代码，便可以将需要的数据以csv格式导出到执行的文件中。

                                                                                                                                                                                                                                                                 ----- yt.Liu