JDBC常用API小结

2021-12-17 22:12:17

建立数据库链接的三种方式：

package com.victor_01;

import java.sql.Connection;

import java.sql.Driver;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.util.Properties;

import org.junit.Test;

public class Test1 {

	private String url = "jdbc:mysql://192.168.244.144:3306/test";

	private String user = "root";

	private String password = "123456";

	@Test

	public void test1() throws Exception {

		Driver driver = new com.mysql.jdbc.Driver();

		Properties prop = new Properties();

		prop.setProperty("user", user);

		prop.setProperty("password", password);

		Connection conn = driver.connect(url, prop);

		System.out.println(conn);

	}

	@Test

	public void test2() throws SQLException{

		Driver driver = new com.mysql.jdbc.Driver();

		DriverManager.registerDriver(driver);

		Connection conn = DriverManager.getConnection(url, user, password);

		System.out.println(conn);

	}

	@Test

	public void test3() throws Exception {

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

		Connection conn = DriverManager.getConnection(url, user, password);

		System.out.println(conn);

	}

}

推荐使用第三种，其实第二种和第三种本质上是一种，后者是前者的优化版。

JDBC接口核心的API

 JDBC接口核心的API

                java.sql.*   和  javax.sql.*

            |- Driver接口： 表示java驱动程序接口。所有的具体的数据库厂商要来实现此接口。

                |- connect(url, properties):  连接数据库的方法。

                        url: 连接数据库的URL

                            URL语法： jdbc协议:数据库子协议://主机:端口/数据库

                            user： 数据库的用户名

                            password： 数据库用户密码

            |- DriverManager类： 驱动管理器类，用于管理所有注册的驱动程序

                |-registerDriver(driver)  : 注册驱动类对象

                |-Connection getConnection(url,user,password);  获取连接对象

            |- Connection接口： 表示java程序和数据库的连接对象。

                    |- Statement createStatement() ： 创建Statement对象

                    |- PreparedStatement prepareStatement(String sql)  创建PreparedStatement对象

                    |- CallableStatement prepareCall(String sql) 创建CallableStatement对象

            |- Statement接口： 用于执行静态的sql语句

                    |- int executeUpdate(String sql)  ： 执行静态的更新sql语句（DDL，DML）

                    |- ResultSet executeQuery(String sql)  ：执行的静态的查询sql语句（DQL）

                |-PreparedStatement接口：用于执行预编译sql语句

                        |- int executeUpdate() ： 执行预编译的更新sql语句（DDL，DML）

                        |-ResultSet executeQuery()  ： 执行预编译的查询sql语句（DQL）

                    |-CallableStatement接口：用于执行存储过程的sql语句（call xxx）

                            |-ResultSet executeQuery()  ： 调用存储过程的方法

            |- ResultSet接口：用于封装查询出来的数据

                    |- boolean next() ： 将光标移动到下一行

                    |-getXX() : 获取列的值

Statement接口

package com.victor_01;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import org.junit.Test;

public class Demo2 {

	private String url = "jdbc:mysql://192.168.244.144:3306/test";

	private String user = "root";

	private String password = "123456";

	@Test

	public void Test1() {

		Connection conn = null;

		Statement stmt = null;

		ResultSet rs = null;

		try {

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

			conn = DriverManager.getConnection(url, user, password);

			stmt = conn.createStatement();

			String sql = "drop table if exists jdbc_test";

			int result = stmt.executeUpdate(sql);

			System.out.println("Drop table:" + result);

			sql = "create table jdbc_test(id int,name varchar(10))";

			result = stmt.executeUpdate(sql);

			System.out.println("Create result:" + result);

			sql = "insert into jdbc_test values(1,'hello')";

			result = stmt.executeUpdate(sql);

			System.out.println("Insert result:" + result);

			int id = 2;

			String name = "world";

			sql = "insert into jdbc_test values(" + id + ",'" + name + "')";

			result = stmt.executeUpdate(sql);

			System.out.println("Insert result2:" + result);

			name = "java";

			sql = "update jdbc_test set name='" + name + "' where id=" + id

					+ "";

			result = stmt.executeUpdate(sql);

			System.out.println("Update result:" + result);

			sql = "select * from jdbc_test";

			rs = stmt.executeQuery(sql);

			while (rs.next()) {

				System.out

						.println(rs.getInt("id") + " " + rs.getString("name"));

			}

		} catch (Exception e) {

			e.printStackTrace();

		} finally {

			if (rs != null) {

				try {

					rs.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			}

			if (stmt != null)

				try {

					stmt.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

			if (conn != null)

				try {

					conn.close();

				} catch (SQLException e) {

					e.printStackTrace();

				}

		}

	}

}

PreparedStatement接口

package com.victor_01;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import org.junit.Test;

public class Demo3 {

	private String url="jdbc:mysql://192.168.244.144:3306/test";

	private String user="root";

	private String password="123456";

	@Test

	public void PreparedStatementTest() throws Exception{

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

	   Connection conn=DriverManager.getConnection(url, user, password);

	   String sql="insert into jdbc_test(id,name) values(?,?)";

	   PreparedStatement prestmt =conn.prepareStatement(sql);

	   prestmt.setInt(1, 3);

	   prestmt.setString(2,"tom");

	   int result=prestmt.executeUpdate();

	   System.out.println(result);

	   sql="update jdbc_test set name=? where id=?";

	   prestmt=conn.prepareStatement(sql);

	   prestmt.setString(1, "steve");

	   prestmt.setInt(2, 3);

	   result=prestmt.executeUpdate();

	   System.out.println(result);

	   sql="select * from jdbc_test where id=?";

	   prestmt=conn.prepareStatement(sql);

	   prestmt.setInt(1, 3);

	   ResultSet rs= prestmt.executeQuery();

	   while(rs.next()){

		   System.out.println("id="+rs.getInt(1)+";name="+rs.getString(2));

	   }

	   rs.close();

	   prestmt.close();

	   conn.close();

	   }

	}

CallableStatement接口

package com.victor_01;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

import org.junit.Test;

public class Demo3 {

	private String url="jdbc:mysql://192.168.244.144:3306/test";

	private String user="root";

	private String password="123456";

	@Test

	public void PreparedStatementTest() throws Exception{

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

	   Connection conn=DriverManager.getConnection(url, user, password);

       String sql="drop procedure findById";

	   Statement stmt=conn.createStatement();

	   int result=stmt.executeUpdate(sql);

	   System.out.println("drop result:="+result);

	   sql=" CREATE PROCEDURE findById(IN sid INT) BEGIN select * from jdbc_test where id=sid; end";

	   stmt=conn.createStatement();

	   result=stmt.executeUpdate(sql);

	   System.out.println("create result:="+result);

	   //直接调用存储过程

	   sql="call findById(2)";

	   CallableStatement stmt1=conn.prepareCall(sql);

	   ResultSet rs=stmt1.executeQuery();

	   while(rs.next()){

		   System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2));

	   }

	   //传参调用存储过程

	   sql="call findById(?)";

	   stmt1=conn.prepareCall(sql);

	   stmt1.setInt(1, 4);

	   rs=stmt1.executeQuery();

	   while(rs.next()){

		   System.out.println("id="+rs.getInt(1)+",name="+rs.getString(2));

	   }

	   //带有输出参数的存储过程

	   sql="drop procedure findById1";

	   stmt=conn.createStatement();

	   result=stmt.executeUpdate(sql);

	   System.out.println("drop findById1 result:"+result);

	   sql=" CREATE PROCEDURE findById1(IN sid INT,OUT sname VARCHAR(10)) BEGIN select name into sname from jdbc_test where id=sid; end";

	   stmt=conn.createStatement();

	   result=stmt.executeUpdate(sql);

	   System.out.println("create result:="+result);

	   sql="call findById1(?,?)";

	   stmt1=conn.prepareCall(sql);

	   stmt1.setInt(1, 4);

	   stmt1.registerOutParameter(2, java.sql.Types.VARCHAR);

	   stmt1.executeQuery(); //注意：结果不是返回到ResultSet中，而是返回到输出参数中。

	   String sname=stmt1.getString(2);

	   System.out.println(sname);

	   rs.close();

	   stmt1.close();

	   stmt.close();

	   conn.close();

	   }

	}

码农公寓

相关文章