Java 数据库编程

// 构建 Java 和数据库之间的桥梁介质

try{            

    Class.forName("com.mysql.jdbc.Driver");

}catch(ClassNotFoundException e1){

    // 注册失败

    e1.printStackTrace();

    return;

}

String url="jdbc:mysql://localhost:3306/test";        

Connection conn = null;

try {

    // 构建 Java 和数据库之间的桥梁：URL，用户名，密码

    conn = DriverManager.getConnection(url, "root", "123456");

    // 构建数据库执行者

    Statement stmt = conn.createStatement(); 

    System.out.println("创建Statement成功！");      

    // 执行 SQL 语句并返回结果到 ResultSet

    ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");

    //update

    int result = stmt.executeUpdate("update t_book set price = 300 where bookid = 1");

    result = stmt.executeUpdate("insert into t_book(bookid, bookname, price) values(4, '编译原理', 90)");

    result = stmt.executeUpdate("delete from t_book where id = 4");

    // 开始遍历 ResultSet 数据

    while(rs.next()) {

        System.out.println(rs.getInt(1) + "," + rs.getString(2) + "," + rs.getInt("price"));

    }

    rs.close();

    stmt.close();

} catch (SQLException e){

    e.printStackTrace();

} finally {

    try {

        if(null != conn){

            conn.close();

        }

    }

    catch (SQLException e){

        e.printStackTrace();

    }            

}

conn.setAutoCommit(false);

insertBook(conn, "insert into t_book values(101, 'aaaa', 10)");

insertBook(conn, "insert into t_book values(102, 'bbbb', 10)");

insertBook(conn, "insert into t_book values(103, 'cccc', 10)");

Savepoint phase1 = conn.setSavepoint(); // 设置一个保存点

insertBook(conn, "insert into t_book values(104, 'cccc', 10)");

insertBook(conn, "insert into t_book values(105, 'cccc', 10)");

conn.rollback(phase1);  // 回滚到 phase1 保存点，即上面 2 行无效

conn.commit();

public static void insertBook(Connection conn, String sql) {

    try {

        // 构建数据库执行者

        Statement stmt = conn.createStatement();

        // 执行 SQL 语句

        int result = stmt.executeUpdate(sql);

        stmt.close();

    } catch (SQLException e) {

        e.printStackTrace();

    }

}

// 构建 Java 和数据库之间的桥梁：URL，用户名，密码

Connection conn = DriverManager.getConnection(url, "root", "123456");

// 使用 “?” 代替字符串拼接

String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";

// 构建数据库执行者

PreparedStatement pstmt = conn.prepareStatement(sql);

// 执行 SQL 语句

int bookid = 10;

String bookName = "Effective Java',50);delete from t_book;insert into t_book values(101, 'faked book";

int price = 50;

// 设定 value 参数

pstmt.setInt(1, bookid);

pstmt.setString(2, bookName);

pstmt.setInt(3, price);

int result = pstmt.executeUpdate();

pstmt.close();

Connection conn = DriverManager.getConnection(url, "root", "123456");

String sql = "insert into t_book(bookid,bookname,price) values(?,?,?)";

// 构建数据库执行者

PreparedStatement pstmt = conn.prepareStatement(sql);

// 执行 SQL 语句

String bookName = "aaaaaaaaaaaaaaaa";

int price = 50;

//values(1, 'Effective Java', 50)

for(int i=200;i<210;i++)

{

    pstmt.setInt(1, i);

    pstmt.setString(2, bookName);

    pstmt.setInt(3, price);

    pstmt.addBatch();

}            

pstmt.executeBatch();

pstmt.close();

// 构建 Java 和数据库之间的桥梁：URL，用户名，密码

Connection conn = DriverManager.getConnection(url, "root", "123456");

// 构建数据库执行者

Statement stmt = conn.createStatement();    

// 执行 SQL 语句并返回结果到 ResultSet

ResultSet rs = stmt.executeQuery("select bookid, bookname, price from t_book order by bookid");

// 获取结果集的元数据

ResultSetMetaData meta = rs.getMetaData(); 

int cols = meta.getColumnCount(); 

for(int i=1;i<=cols;i++)

{

    System.out.println(meta.getColumnName(i) + "," + meta.getColumnTypeName(i));

}

rs.close();

stmt.close();

// 从 c3p0 获取

conn = C3p0Factory.getConnection();

//C3p0Factory

public class C3p0Factory {

    private static ComboPooledDataSource dataSource = null;

    public static void init() throws Exception {

        dataSource = new ComboPooledDataSource();

        // 配置文件可以放在 xml 文件中

        //dataSource 会自动加载 resources 文件夹中 c3p0-config.xml 文件（如果存在）

        dataSource.setDriverClass( "com.mysql.jdbc.Driver" );            

        dataSource.setJdbcUrl( "jdbc:mysql://localhost:3306/test" );

        dataSource.setUser("root");                                  

        dataSource.setPassword("123456");                               

        // the settings below are optional -- c3p0 can work with defaults

        dataSource.setMinPoolSize(5);                                   

        dataSource.setAcquireIncrement(5);

        dataSource.setMaxPoolSize(20);

    }

    public static Connection getConnection() throws Exception {

        if(null == dataSource) {

            init();

        }

       return dataSource.getConnection();

    }

}

// 从 Druid 获取

conn = DruidFactory.getConnection();

public class DruidFactory1 {

    private static DruidDataSource dataSource = null;

    public static void init() throws Exception {

        // 配置文件可以放在 properties 文件中

        //Properties properties = new Properties();

        //InputStream in = DruidFactory2.class.getClassLoader().getResourceAsStream("druid.properties");  

        //properties.load(in);         

        //dataSource = (DruidDataSource)DruidDataSourceFactory.createDataSource(properties);    

        dataSource = new DruidDataSource();    

        dataSource.setDriverClassName("com.mysql.jdbc.Driver"); 

        dataSource.setUsername("root");

        dataSource.setPassword("123456");

        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/test"); 

        dataSource.setInitialSize(5);

        dataSource.setMinIdle(1); 

        dataSource.setMaxActive(10); 

        // 启用监控统计功能 dataSource.setFilters ("stat");// 

    }

    public static Connection getConnection() throws Exception {

        if(null == dataSource) {

            init();

        }

        return dataSource.getConnection();

    }

}

// 构建数据库执行者

Statement stmt = conn.createStatement();