JDBC数据库连接实例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
package com.sungoal.etl;

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

public class JDBCTest {

	// 创建一个数据库连接
	public static Connection getConnection() {
		Connection connection = null;
		String USERNAMR = "sungoal2";
		String PASSWORD = "sungoal2";
		String DRVIER = "oracle.jdbc.OracleDriver";
		String URL = "jdbc:oracle:thin:@192.168.12.203:1521:orcl";
		try {
			Class.forName(DRVIER);
			connection = DriverManager.getConnection(URL, USERNAMR, PASSWORD);
			System.out.println("成功连接数据库");
		} catch (ClassNotFoundException e) {
			throw new RuntimeException(e);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return connection;
	}

	// 关闭资源
	public static void close(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void close(PreparedStatement preparedStatement) {
		try {
			if (preparedStatement != null) {
				preparedStatement.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void close(ResultSet resultSet) {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void main(String[] args) {
		Connection connection = JDBCTest.getConnection();
		// 准备sql语句
		String sql = "selec * from student";
		// 创建语句传输对象
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			System.out.println(sql);
			connection.setAutoCommit(false);
			preparedStatement = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
			preparedStatement.setFetchSize(1000); // 每次获取1万条记录
			// preparedStatement.setFetchDirection(ResultSet.FETCH_REVERSE);
			resultSet = preparedStatement.executeQuery();
			System.out.println("ps.getQueryTimeout():" + preparedStatement.getQueryTimeout());
			System.out.println("ps.getFetchSize():" + preparedStatement.getFetchSize());
			System.out.println("ps.getFetchDirection():" + preparedStatement.getFetchDirection());
			System.out.println("ps.getMaxFieldSize():" + preparedStatement.getMaxFieldSize());
			System.out.println("查询成功---" + resultSet.toString());
			int col = resultSet.getMetaData().getColumnCount();
			// System.out.println(col);
			int m = 0;
			while (resultSet.next()) {
				for (int i = 1; i <= col; i++) {
					System.out.print(resultSet.getString(i));
					System.out.print("tt");
				}
				m++;
				System.out.println("数据--" + m);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCTest.close(resultSet);
			JDBCTest.close(preparedStatement);
			JDBCTest.close(connection);
		}
	}
}