package com.amuos.jdbc.curd;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import com.amuos.jdbc.util.JdbcUtils;

/**

 *

 * 2015-1-25

 *

 * @author <a href="mailto:472846889@qq.com">王娟</a>

 *

 */

public class CRUD {

    /**

     * @param args

     * @throws SQLException

     */

//    public static void main(String[] args) throws SQLException {

////         create();

////         read();

////         update();

////         delete();

//    }

    static void delete() throws SQLException {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 2.建立连接

            conn = JdbcUtils.getConnection();

            // conn = JdbcUtilsSing.getInstance().getConnection();

            // 3.创建语句

            st = conn.createStatement();

            String sql = "delete from contacts where id = 2";

            // 4.执行语句

            int i = st.executeUpdate(sql);

            System.out.println("Have deleted " + i + " row.");

        } finally {

            JdbcUtils.free(rs, st, conn);

        }

    }

    static void update() throws SQLException {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 2.建立连接

            conn = JdbcUtils.getConnection();

            // conn = JdbcUtilsSing.getInstance().getConnection();

            // 3.创建语句

            st = conn.createStatement();

            String sql = "update contacts set mail2 = 'test@163.com' where name = 'test' ";

            // 4.执行语句

            int i = st.executeUpdate(sql);

            System.out.println("Have updated " + i + " row.");

        } finally {

            JdbcUtils.free(rs, st, conn);

        }

    }

    static void create() throws SQLException {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 2.建立连接

            conn = JdbcUtils.getConnection();

            // conn = JdbcUtilsSing.getInstance().getConnection();

            // 3.创建语句

            st = conn.createStatement();

            String sql = "insert into contacts(name,main,mail1,mail2,relationship) values ('test', '1234565', 'test@test.com','','同事') ";

            // 4.执行语句

            int i = st.executeUpdate(sql);

            System.out.println("Have inserted " + i + " row into table contacts.");

        } finally {

            JdbcUtils.free(rs, st, conn);

        }

    }

    static void read() throws SQLException {

        Connection conn = null;

        Statement st = null;

        ResultSet rs = null;

        try {

            // 2.建立连接

            conn = JdbcUtils.getConnection();

            // conn = JdbcUtilsSing.getInstance().getConnection();

            // 3.创建语句

            st = conn.createStatement();

            // 4.执行语句

            rs = st.executeQuery("select id, name, birthday, mobile  from contacts");

            // 5.处理结果

            while (rs.next()) {

                System.out.println(rs.getObject("id") + "\t"

                        + rs.getObject("name") + "\t"

                        + rs.getObject("birthday") + "\t"

                        + rs.getObject("mobile"));

            }

        } finally {

            JdbcUtils.free(rs, st, conn);

        }

    }

}