/*
sql server:
    String className ="com.microsoft.sqlserver.jdbc.SQLServerDriver";
mysql:
    String className = "com.mysql.jdbc.Driver";
*/
Class.forName(className);

/*
sql server:
    jdbcUrl = "jdbc:sqlserver://localhost:1433;DatabaseName=jdbc_research";
mysql:
    jdbcUrl = "jdbc:mysql://localhost:3306/jdbc_research?useUnicode=true&characterEncoding=UTF-8";
*/
Connection c = DriverManager.getConnection(jdbcUrl,username,password);

Statement stm = c.createStatement();
//创建语句
stm.execute("CREATE table jdbc_test(name CHAR(20))");
//增 删 改 查
stm.executeUpdate("INSERT INTO jdbc_test VALUES('TEST')");
stm.executeUpdate("DELETE jdbc_test");
stm.executeUpdate("UPDATE jdbc_test SET name='change' WHERE name = 'TEST'");
ResultSet rs =stm.executeQuery("SELECT * FROM jdbc_test");
if(rs.next())
    System.out.println(rs.getString(1));//结果集从1开始

//适合于带参数多次执行的数据库操作
PreparedStatement  stm = c.prepareStatement("INSERT INTO jdbc_test VALUES(?)");
for (int i = 0; i < 10; i++) {
    //参数索引从1开始
    stm.setString(1,"test" + i);
    stm.executeUpdate();
}

//批处理
PreparedStatement  stm = c.prepareStatement("INSERT INTO jdbc_test VALUES(?)");
for (int i = 0; i < 10; i++) {
    //参数索引从1开始
    stm.setString(1,"Batch" + i);
    stm.addBatch();
}
stm.executeBatch();

//参数索引从1开始
String procedure = "{ call get_name_by_id(?,?,?) }";
//设置调用的存储过程
CallableStatement stm = c.prepareCall(procedure);
//注册输入参数的类型
stm.setInt(1, 2);
//注册输出的参数
stm.registerOutParameter(2, java.sql.Types.CHAR);
stm.registerOutParameter(3, java.sql.Types.INTEGER);
stm.execute(); 

//获取对应输出参数的值
String rs = stm.getString(2);
System.out.println("name:"+rs);
int rs2 = stm.getInt(3);
System.out.println("id:"+rs2);

CREATE table proc_test(name CHAR(20),id int,sex char(2));
INSERT INTO proc_test (name,id,sex) VALUES('test',1,'男');
INSERT INTO proc_test (name,id,sex) VALUES('test2',2,'女');
INSERT INTO proc_test (name,id,sex) VALUES('test3',3,'男');
INSERT INTO proc_test (name,id,sex) VALUES('test4',4,'女');

--CREATE
ALTER PROC get_name_by_id(@n INT,@rs CHAR(20) OUTPUT,@id INT OUTPUT)
AS
BEGIN
    SELECT @rs=name, @id=id FROM proc_test WHERE id = @n
END

--SQL调用
DECLARE @r CHAR(20)
DECLARE @id INT
EXEC get_name_by_id 2,@r OUTPUT ,@id OUTPUT
SELECT @r AS name ,@id AS id

c.close();