java 连接各种数据库大全

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