mysql
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class testMysql {
public static void main(String[] args) {
String tableName="student";
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/fuck?useUnicode=true&characterEncoding=UTF-8&createDatabaseIfNotExist=true";
String user = "root";
String password = "root";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
Statement statement = conn.createStatement();
statement.executeUpdate("drop table if exists "+tableName);
statement.executeUpdate("create table "+tableName+"(name varchar(20),city varchar(20),password varchar(20))");
statement.executeUpdate("insert into "+tableName+" values ('110','120','130')");
statement.executeUpdate("insert into "+tableName+" values ('你是猪','我是英文test','130')");
String sql = "select * from "+tableName;
ResultSet rs = statement.executeQuery(sql);
System.out.println("-----------------");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t"+rs.getString(2)+"\t"+rs.getString(3));
}
statement.close();
rs.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
package junit.test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class testMysql {
public static void main(String[] args) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/mysql";
String user = "root";
String password = "root";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed())
System.out.println("Succeeded connecting to the Database!");
Statement statement = conn.createStatement();
String sql = "select * from user";
ResultSet rs = statement.executeQuery(sql);
System.out.println("-----------------");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t"+rs.getString(2)+"\t"+rs.getString(3));
}
statement.close();
rs.close();
conn.close();
}catch (Exception e) {
e.printStackTrace();
}
}
}
sql server 2008
private static void testsql2008() throws Exception {
String JDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// SQL数据库引擎
String connectDB = "jdbc:sqlserver://192.168.0.68:1433;DatabaseName=Mydb";// 数据源
String user = "sa";
String password = "WJH0774wjh";
Class.forName(JDriver);// 加载数据库引擎,返回给定字符串名的类
Connection con = DriverManager.getConnection(connectDB, user, password);// 连接数据库对象
System.out.println("连接数据库成功");
Statement stmt = con.createStatement();// 创建SQL命令对象
System.out.println("开始创建表");
String query = "create table TABLE1(ID NCHAR(2),NAME NCHAR(10))";// 创建表SQL语句
stmt.executeUpdate("drop table TABLE1");
stmt.executeUpdate(query);// 执行SQL命令对象
System.out.println("表创建成功");
System.out.println("开始插入数据");
String a1 = "INSERT INTO TABLE1 VALUES('1','旭哥')";// 插入数据SQL语句
String a2 = "INSERT INTO TABLE1 VALUES('2','伟哥')";
String a3 = "INSERT INTO TABLE1 VALUES('3','张哥')";
stmt.executeUpdate(a1);// 执行SQL命令对象
stmt.executeUpdate(a2);
stmt.executeUpdate(a3);
System.out.println("插入数据成功");
System.out.println("开始读取数据");
ResultSet rs = stmt.executeQuery("SELECT * FROM TABLE1");// 返回SQL语句查询结果集(集合)
// 循环输出每一条记录
while (rs.next()) {
System.out.println(rs.getString("ID") + "\t" + rs.getString("NAME"));
}
System.out.println("读取完毕");
// 关闭连接
stmt.close();// 关闭命令对象连接
con.close();// 关闭数据库连接
}
package com.jiepu.testsql;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**java 连接sql 数据库大全、可以用来测试各种数据库是否安装运行成功
* 本项目基于maven管理 查询jar信息http://search.maven.org/
* 在mvn下运行 mvn exec:java -Dexec.mainClass="com.jiepu.testsql.App"
*/
public class App {
public static void main(String[] args) {
try {
System.out.println("Hello World!");
testmysql();
testsql2008();
testjtds_sql2008();
testOracle();
//testSybase();
// testdb2();
testAccess();
testSqlite();
testDerby();
// testPostgreSQL();
} catch (Exception ex) {
ex.printStackTrace();
}
}
//maven添加sql2008 jdbc4 http://claude.betancourt.us/add-microsoft-sql-jdbc-driver-to-maven/
private static void testsql2008() throws Exception {
String JDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";// SQL数据库引擎
String connectDB = "jdbc:sqlserver://192.168.0.68:1433;DatabaseName=Mydb";// 数据源
String user = "sa";
String password = "WJH0774wjh";
Class.forName(JDriver);// 加载数据库引擎,返回给定字符串名的类
Connection con = DriverManager.getConnection(connectDB, user, password);// 连接数据库对象
System.out.println("连接数据库成功");
Statement stmt = con.createStatement();// 创建SQL命令对象
System.out.println("开始创建表");
String query = "create table TABLE1(ID NCHAR(2),NAME NCHAR(10))";// 创建表SQL语句
stmt.executeUpdate("drop table TABLE1");
stmt.executeUpdate(query);// 执行SQL命令对象
System.out.println("表创建成功");
System.out.println("开始插入数据");
String a1 = "INSERT INTO TABLE1 VALUES('1','旭哥')";// 插入数据SQL语句
String a2 = "INSERT INTO TABLE1 VALUES('2','伟哥')";
String a3 = "INSERT INTO TABLE1 VALUES('3','张哥')";
stmt.executeUpdate(a1);// 执行SQL命令对象
stmt.executeUpdate(a2);
stmt.executeUpdate(a3);
System.out.println("插入数据成功");
System.out.println("开始读取数据");
ResultSet rs = stmt.executeQuery("SELECT * FROM TABLE1");// 返回SQL语句查询结果集(集合)
// 循环输出每一条记录
while (rs.next()) {
System.out.println(rs.getString("ID") + "\t" + rs.getString("NAME"));
}
System.out.println("读取完毕");
// 关闭连接
stmt.close();// 关闭命令对象连接
con.close();// 关闭数据库连接
}
private static void testjtds_sql2008() {
String driver = "net.sourceforge.jtds.jdbc.Driver";
String url = "jdbc:jtds:sqlserver://192.168.0.68:1433/mydb";
String user = "sa";
String password = "WJH0774wjh";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to the Database!");
}
Statement statement = conn.createStatement();
String sql = "select * from TABLE1";
ResultSet rs = statement.executeQuery(sql);
System.out.println("-----------------");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2));
}
statement.close();
rs.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
private static void testmysql() {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://127.0.0.1:3306/mysql";
String user = "root";
String password = "root";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection(url, user, password);
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to the Database!");
}
Statement statement = conn.createStatement();
String sql = "select * from user";
ResultSet rs = statement.executeQuery(sql);
System.out.println("-----------------");
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
}
statement.close();
rs.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
//maven 添加ojdbc6.jar http://www.cnblogs.com/leiOOlei/archive/2013/10/21/3380568.html
private static void testOracle() {
Connection con = null;// 创建一个数据库连接
PreparedStatement pre = null;// 创建预编译语句对象,一般都是用这个而不用Statement
ResultSet result = null;// 创建一个结果集对象
try {
Class.forName("oracle.jdbc.driver.OracleDriver");// 加载Oracle驱动程序
System.out.println("开始尝试连接数据库!");
String url = "jdbc:oracle:thin:@192.168.0.68:1521:orcl";// 127.0.0.1是本机地址,XE是精简版Oracle的默认数据库名
String user = "EVIDENCESYS";// 用户名,系统默认的账户名
String password = "abcABC123";// 你安装时选设置的密码
con = DriverManager.getConnection(url, user, password);// 获取连接
System.out.println("连接成功!");
String sql = "select * from case_info";// 预编译语句,“?”代表参数
pre = con.prepareStatement(sql);// 实例化预编译语句
result = pre.executeQuery();
while (result.next()) // 当结果集不为空时
{
System.out.println(result.getString(1) + "," + result.getString(2));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (result != null) {
result.close();
}
if (pre != null) {
pre.close();
}
if (con != null) {
con.close();
}
System.out.println("数据库连接已关闭!");
} catch (Exception e) {
e.printStackTrace();
}
}
}
//http://hamlzf.iteye.com/blog/1169061
//该数据库我没有安装和测试
private static void testdb2() {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.ibm.db2.jcc.DB2Driver").newInstance();
conn = DriverManager.getConnection("jdbc:db2://localhost:50000/express", "acer", "abin");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from vote");
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getString(2));
System.out.println(rs.getString(3));
}
} catch (Exception e) {
e.printStackTrace();
}
}
//该数据库我没有安装和测试
private static void testSybase() {
try {
Class.forName("com.sybase.jdbc2.jdbc.SybDriver").newInstance();
String url = "jdbc:sybase:Tds:192.168.0.168:5000/master";// 数据库名
Properties sysProps = System.getProperties();
sysProps.put("user", "sa"); // 设置数据库访问用户名
sysProps.put("password", "sybase"); // 密码
Connection conn = DriverManager.getConnection(url, sysProps);
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
String sql = "select id,name,crdate from dbo.sysobjects where type='U'"; // 表
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println("oject_id:" + rs.getString(1) + ",oject_name:" + rs.getString(2)); // 取得第二列的值
}
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
private static void testAccess() throws Exception {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=d:\\jdbc\\test.mdb";
Connection conn = DriverManager.getConnection(url, "", "");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM student");//students
//rs.beforeFirst();
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
}
rs.close();
stmt.close();
conn.close();
}
private static void testSqlite() throws Exception {
Class.forName("org.sqlite.JDBC");
Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists people;");
stat.executeUpdate("create table people (name, occupation);");
PreparedStatement prep = conn.prepareStatement(
"insert into people values (?, ?);");
prep.setString(1, "Gandhi");
prep.setString(2, "politics");
prep.addBatch();
prep.setString(1, "Turing");
prep.setString(2, "computers");
prep.addBatch();
prep.setString(1, "Wittgenstein");
prep.setString(2, "你是猪");
prep.addBatch();
conn.setAutoCommit(false);
prep.executeBatch();
conn.setAutoCommit(true);
ResultSet rs = stat.executeQuery("select * from people;");
while (rs.next()) {
System.out.println("name = " + rs.getString("name"));
System.out.println("job = " + rs.getString("occupation"));
}
rs.close();
conn.close();
}
public static boolean hasTable(String table, Connection conn) throws Exception {
if (conn == null) {
throw new SQLException("Connection has not been established!");
}
boolean state = false;
DatabaseMetaData meta = conn.getMetaData();
ResultSet set;//= meta.getTables(null, null, table, null);
set = meta.getTables(null, null, table.toUpperCase(), null);
while (set.next()) {
state = true;
System.out.println("Has Table " + table);
}
return state;
}
private static void testDerby() throws Exception {
String driver = "org.apache.derby.jdbc.EmbeddedDriver";
Class.forName(driver);
Connection conn = DriverManager.getConnection("jdbc:derby:userDB;create=true;user=test;password=test"); //userDB创建在d盘下面的a/b文件夹下面,
if (!conn.isClosed()) {
System.out.println("Succeeded connecting to the Database!");
}
Statement stat = conn.createStatement();
if (hasTable("tb_user", conn)) {
stat.executeUpdate("drop table tb_user");
}
String sql_create = "create table tb_user(id int,name varchar(20),password varchar(20))";
stat.execute(sql_create);
stat.close();
String sql_insert = "insert into tb_user values (?,?,?)";
PreparedStatement pst = conn.prepareStatement(sql_insert);
pst.setInt(1, 1);
pst.setString(2, "name测试的");
pst.setString(3, "pwd 大全");
pst.executeUpdate();
pst = conn.prepareStatement("select * from tb_user");
ResultSet rs = pst.executeQuery();
while (rs.next()) {
int id = rs.getInt("id");
String name = rs.getString("name");
String pwd = rs.getString("password");
System.out.println("id->" + id + "\tname->" + name + "\tpassword->" + pwd);
}
rs.close();
}
//我没有安装和测试该数据库
private static void testPostgreSQL() {
System.out.print(" this is a test ");
try {
Class.forName("org.postgresql.Driver").newInstance();
String url = " jdbc:postgresql://localhost:5432/postgres ";
Connection con = DriverManager.getConnection(url, " postgres ", " 1234 ");
Statement st = con.createStatement();
String sql = " select * from testtable ";
ResultSet rs = st.executeQuery(sql);
while (rs.next()) {
System.out.print(rs.getInt(1));
System.out.println(rs.getString(2));
}
rs.close();
st.close();
con.close();
} catch (Exception ee) {
System.out.print(ee.getMessage());
}
}
}