jdbc

2019独角兽企业重金招聘Python工程师标准>>>

准备

• 连接数据库，将对应的数据库驱动jar包导入项目
• SQL脚本

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `password` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'liyuhang', '123321');
INSERT INTO `user` VALUES ('2', 'sunmiaomiao', '654321');

总述

Connection:表示一个数据库连接

Statement：用来执行SQL

PreparedStatement：用来执行可复用SQL，主要是可以传参

CallableStatement:用来执行存储过程

ResultSet:表示SQL执行的结果集

ResultSetMetaData：结果集的元数据，用来获取结果集的相关信息

连接数据库

直接上代码

package json712.study_jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JdbcUtils {
	private static final String url = "jdbc:mysql://192.168.1.103:3306/study_web?useSSL=false";
	private static final String user = "json";
	private static final String password = "123456";
	static{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		try {
			Connection connection=DriverManager.getConnection(url, user, password);
			return connection;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
};

执行SQL

Statement

	@Test
	public void queryTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			Statement statement=connection.createStatement();
			String sql="select * from user ";
			ResultSet resultSet=statement.executeQuery(sql);
			while (resultSet.next()) {
				int id=resultSet.getInt(1);
				String name=resultSet.getString(2);
				System.out.println("id:"+id+" name:"+name);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

运行结果:

id:1 name:liyuhang
id:2 name:sunmiaomiao

PreparedStatement

@Test
	public void prepareStatementTest(){
		Connection connection=JdbcUtils.getConnection();
		String sql=" select * from user where id=?";
		try {
			PreparedStatement statement=connection.prepareStatement(sql);
			statement.setInt(1, 1);
			ResultSet resultSet=statement.executeQuery();
			while (resultSet.next()) {
				int id=resultSet.getInt(1);
				String name=resultSet.getString(2);
				String password=resultSet.getString(3);
				System.out.println("id:"+id+" name:"+name+" password:"+password);
			}
			statement.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

运行结果：

id:1 name:liyuhang password:123321

批量执行SQL

	@Test
	public void inserBatchTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			PreparedStatement statement=connection.prepareStatement("INSERT INTO `user`\n" +
					"(`name`,`PASSWORD`)\n" +
					"VALUES\n" +
					"	(?, ?)");
			String[] name=new String[]{"wt","gaojl"};
			String[] password=new String[]{"031512","222222"};
			for (int i = 0; i < password.length; i++) {
				statement.setString(1, name[i]);
				statement.setString(2, password[i]);
				statement.addBatch();
			}
			statement.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

执行存储过程

CallableStatement

执行一个没有输入输出的存储过程

脚本准备

CREATE PROCEDURE `hi`()
SELECT 'hello'

测试代码：

	@Test
	public void callprocedureTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			CallableStatement prepareCall = connection.prepareCall("call hi()");
			prepareCall.execute();
			ResultSet resultSet=prepareCall.getResultSet();
			while (resultSet.next()) {
				String hl=resultSet.getString(1);
				System.out.println(hl);
			}
			prepareCall.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

运行结果：

hello

执行一个有输入参数的存储过程

脚本准备

CREATE  PROCEDURE `pr_add`(
   a int,
   b int
)
begin
   declare c int;
   if a is null then
      set a = 0;
   end if;
   if b is null then
      set b = 0;
   end if;
   set c = a + b;
   select c as sum;
   
end

测试代码：

	@Test
	public void callprocedureInTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			CallableStatement statement	=connection.prepareCall("call pr_add(?,?)");
			statement.setInt(1, 10);
			statement.setInt(2, 20);
			statement.execute();
			ResultSet resultSet=statement.getResultSet();
			while (resultSet.next()) {
				System.out.println(resultSet.getInt(1));
			}
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

运行结果：

30

执行一个有输入输出的存储过程

脚本准备:

CREATE  PROCEDURE `testoutput`(a int,b int, out c int)
BEGIN 
if a is null then
set a=0;
end if;

if b is null THEN
set b=0;
end if;
set c=a+b;
end

测试代码：

	@Test
	public void callprocedureOutTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			CallableStatement statement	=connection.prepareCall("call testoutput(?,?,?)");
			statement.setInt(1, 10);
			statement.setInt(2, 20);
			statement.registerOutParameter(3, Types.INTEGER) ;  
			statement.execute();
			System.out.println(statement.getInt(3));
			statement.close();
			connection.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

运行结果：

30

ResultSetMetaData

测试代码：

	@Test
	public void metadataTest(){
		Connection connection=JdbcUtils.getConnection();
		try {
			Statement statement=connection.createStatement();
			String sql="select * from user ";
			ResultSet resultSet=statement.executeQuery(sql);
			ResultSetMetaData metadata=resultSet.getMetaData();
			for(int i=1;i<=metadata.getColumnCount();i++){
				String label=metadata.getColumnLabel(i);
				int size=metadata.getColumnDisplaySize(i);
				String columnname=metadata.getColumnName(i);
				String tablename=metadata.getTableName(i);
				String classname=metadata.getColumnClassName(i);
				//metadata.
				//System.out.println("cname:"+columnname+" classname:"+classname);
				System.out.println("label:"+label+" displaysize:"+size+" columnname:"+columnname+" tablename:"+tablename);
			}
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

运行结果：

label:id displaysize:11 columnname:id tablename:user
label:name displaysize:255 columnname:name tablename:user
label:password displaysize:8 columnname:password tablename:user

转载于:https://my.oschina.net/u/3238650/blog/899031