---整理者:唐昭文(有错误的地方请指出来)
1.1.1 java中不同数据库连接字符串
一.连接oracle数据库
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@服务器ip:1521:数据库名称";
Connection conn=DriverManager.getConnection(url,"用户名","密码");
二.连接mssql数据库(sql server 2005)
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=数据库名称";
Connection conn=DriverManager.getConnection(url,"用户名","密码");
三:连接MySQL数据库 (com.mysql.jdbc.Driver)
Class.forName("com.mysql.jdbc.Driver ");
String url ="jdbc:mysql://localhost:3306/数据库名称”;
Connection conn= DriverManager.getConnection(url, “用户名”,”密码”);
四.连接DB2数据库
Class.forName("com.ibm.db2.jdbc.app.DB2Driver ");
String url="jdbc:db2://localhost:5000/数据库名称";
Connection conn= DriverManager.getConnection(url, “用户名”,”密码”);
1.1.2 Java中采用jdbc连接数据库实例
//Oracle
import java.sql.*;
//重点
public class TestJDBC {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//加载驱动第二种写法
//new oracle.jdbc.driver.OracleDriver();
//得到连接
//"jdbc:oracle:thin:@127.0.0.1:1521:MYORA1" 是数据库的连接串,不同的数据库连接串不同
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1", "scott", "tzw1992");
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from dept");
while (rs.next()) {
System.out.println(rs.getString("deptno"));
System.out.println(rs.getInt("deptno"));
}
} catch (ClassNotFoundException e) {
System.out.println("驱动加载不成功!");
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭各种资源
try{
if (rs != null) {
rs.close();
rs = null;
}
if (stmt != null) {
stmt.close();
stmt = null;
}
if (conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
1.1.3 将数据库的连接封装成一个类
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
//这个类的作用为对数据库的操作进行封装,对一些常用的操作进行封装可使代码更简洁
public class DB {
//连接数据库
public static Connection getConn() {
Connection conn = null;
try {
Class.forName( "com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://localhost:1434;databaseName=班费管理系统数据库";
String userName = "sa";
String passWord = "123456789";
conn = DriverManager.getConnection(url, userName, passWord);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//创建Statement对象
public static Statement createStmt(Connection conn) {
Statement stmt = null;
try {
stmt = conn.createStatement();
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
//创建ResultSet对象,用于执行静态的sql语句
public static ResultSet executeQuery(Statement stmt, String sql) {
ResultSet rs = null;
try {
rs = stmt.executeQuery(sql);
} catch (SQLException e) {
e.printStackTrace();
}
return rs;
}
//关闭Connection
public static void close(Connection conn) {
if(conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
//关闭Statement
public static void close(Statement stmt) {
if(stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
stmt = null;
}
}
//关闭ResultSet
public static void close(ResultSet rs) {
if(rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
}
}
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.tzw.cms.util.DB;
public class FindPaginatedTest {
public static void main(String[] args) {
//从第几条开始查询
int offset;
//每页显示多少条记录
int pageSize = 15;
//显示第几页
int pageNo = 2;
//数据库中的数据是从第0条开始计算
offset = (pageNo - 1) * pageSize;
//链接数据库
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = "select * from findpaginated limit ?,?";
try {
Class.forName( "com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/test";
conn = DriverManager.getConnection(url, "root", "root");
pstmt = conn.prepareStatement(sql);
pstmt.setInt( 1, offset);
pstmt.setInt( 2, pageSize);
rs = pstmt.executeQuery();
while(rs.next()) {
System.out.println(rs.getInt( "id") + "\\" + rs.getString( "name"));
}
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if(rs != null) {
rs.close();
rs = null;
}
if(pstmt != null) {
pstmt.close();
pstmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}