JDBC连接

-------------------------------《JDBC》--------------------------------

JDBC Java数据库连接

JDBC是一个规范，遵循JDBC接口规范，各个数据库厂家各自实现自己的驱动程序

JDBC驱动程序都应实现的重要接口：

DriverManager 驱动管理

Connection 数据库连接

Statement 语句执行接口 Statement---sql语句.

CallableStatement---存储过程、函数.

ResultSet 结果集

ODBC 开放数据库连接

 

驱动包放入classpath环境变量中。

 

//Java连接Oracle

"jdbc:oracle:thin:@192.168.2.23:1521:simple","simple","simple"

//Java连接SQLServer

jdbc:microsofr:sqlserver://localhost:1433;DatabaseName=dbName

 

---------------------------------------Java连接Oracle------------------------------------------------------

1、加载驱动

Class.forName("oracle.jdbc.driver.OracleDriver");

2、得到连接

Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@192.168.2.23:1521:simple","simple","simple");

3、创建CallableStatement

CallableStatement cs = ct.prepareCall("{call pro_input(?,?)}");

4、给?赋值

cs.setString(1,"simple");

cs.setInt(2, 20);

5、执行

cs.execute();

ct.commit();

6、关闭

//cs.close();

ct.close();

 

 

完整例子：

 

public class JDBCTest
{
	//1.加载驱动
	static boolean LoadDriver()
	{
		String driverClass="oracle.jdbc.driver.OracleDriver";
		try
		{
			//根据类名 搜索驱动程序
			Class.forName(driverClass);
			return true;
		}
		catch(ClassNotFoundException e)
		{
			System.out.println(e);
		}
		return false;
	}

	//2.数据库连接
	static Connection OpenDataBase(String user,String pwd)
	{
		//定义链接字符串
		String url="jdbc:oracle:thin:@192.168.2.23:1521:simple";
		try
		{
			return DriverManager.getConnection(url,user,pwd);
		}
		catch(SQLException e)
		{
			System.out.println("建立数据库连接失败,"+e);
		}
		return null;
	}
	
	//3.执行语句
	static void testSql(Connection con,String sql)
	{
		try
		{
			Statement stmt = con.createStatement();
			int rows = stmt.executeUpdate(sql);
			System.out.println("执行语句,返回:"+rows);
		} catch (SQLException e)
		{
			System.out.println("执行语句失败,"+e);
		}
	}
	
	//数据库事物控制
	//开始事务
	static void BeginTranaction(Connection con)
	{
		try
		{
			con.setAutoCommit(false);//设置jdbc事务为手动模式
		}
		catch(SQLException e)
		{
			System.out.println("事务初始化失败,"+e);
		}
	}
	//提交
	static void Commit(Connection con)
	{
		try
		{
			con.commit();
		}
		catch(SQLException e)
		{
			System.out.println("事务提交失败,"+e);
		}
		try
		{
			con.setAutoCommit(true);//还原为自动单条事务模式
		}
		catch(SQLException e){}
	}
	//回滚
	static void Rollback(Connection con)
	{
		try
		{
			con.rollback();
		}
		catch(SQLException e)
		{
			System.out.println("事务回滚失败,"+e);
		}
		try
		{
			con.setAutoCommit(true);//还原为自动单条事务模式
		}
		catch(SQLException e){}
	}
	
	//查询
	static ResultSet testQuerry(Connection con,String sql)
	{
		try
		{
			Statement stmt = con.createStatement();
			return stmt.executeQuery(sql);
		}
		catch(SQLException e)
		{
			System.out.println("执行查询发生异常:"+e);
		}
		return null;
	}
	
	//执行预处理的语句对象
	static PreparedStatement getPrapareStmt(Connection con,String sql)
	{
		try
		{
			return con.prepareStatement(sql);
		} catch (SQLException e)
		{
			System.out.println("创建预处理对象发生异常,"+e);
		}
		return null;
	}
	
	//调用存储过程
	static int WriteSyslos(Connection con,int tid,String content)
	{
		try
		{
			String sql = "{call prc_writelog(?,?,?,?)}";
			//String sql = "{?=call prc_writelog(?,?,?,?)}";	//调用函数
			CallableStatement stmt = con.prepareCall(sql);
			//参数赋值
			stmt.setInt(1, tid);
			stmt.setString(2, content);
			//注册出参类型
			stmt.registerOutParameter(3, Types.INTEGER);
			stmt.registerOutParameter(4, Types.VARCHAR);
			//执行
			stmt.execute();
			//取出出参的数据
			int iRet = stmt.getInt(3);
			String sRet = stmt.getString(4);
			System.out.printf("iRet:%d,sRet:%s \r\n",iRet,sRet);
			return iRet;
		}
		catch (SQLException e)
		{
			System.out.println("调用存储过程失败,"+e);
		}
		return 0;
	}
	
	public static void main(String[] args)
	{
		if(LoadDriver())
		{
			Connection dbCon = OpenDataBase("simple","simple");
			if(dbCon!=null)
			{
				System.out.println("连接成功！");
			}
			//写日志
			WriteSyslos(dbCon, 10, "记录java日志,通过存储过程!");
			//开始事务
			BeginTranaction(dbCon);
			//insert
			for(int i = 0;i<3;i++)
			{
				testSql(dbCon, "insert into syslogs values(seq_syslogs.nextval,10,sysdate,'java客户端程序日志',user)");
			}
			//结束事务
			Commit(dbCon);
			//Rollback(dbCon);
			
			//执行批量任务
			try
			{
				PreparedStatement pstmt = getPrapareStmt(dbCon, "insert into syslogs values(seq_syslogs.nextval,?,sysdate,?,user)");
				BeginTranaction(dbCon);//手动事务
				for(int i=0;i<300;i++)
				{
					pstmt.setInt(1, 10);
					pstmt.setString(2, "No."+i+"批量插入任务");
					
					pstmt.addBatch();//添加到批处理任务中
				}
				int[] rows = pstmt.executeBatch();
				System.out.print("批处理任务返回:("+pstmt.SUCCESS_NO_INFO+"表示执行成功, "+pstmt.EXECUTE_FAILED+"表示执行失败!)[");
				for(int val:rows)
				{
					System.out.print(val+",");
				}
				System.out.println("]");
				Commit(dbCon);
				pstmt.close();
			} catch (SQLException e1)
			{
				e1.printStackTrace();
			}
			//update
			//testSql(dbCon, "update syslogs set content='java客户端更新日志' where logid=90");
			//testSql(dbCon, "delete from syslogs where logid=90");
			
			//查询
			try
			{
				ResultSet rs = testQuerry(dbCon, "select log.logid,type.title,log.dtm,log.content from syslogs log,logtypes type where log.tid=type.tid order by log.dtm desc ");
				if(rs!=null)
				{
					while(rs.next())//移到下一条记录
					{
						//int logid_1 = rs.getInt(1);
						int logid_2 = rs.getInt("logid");
						System.out.printf("日志ID :%d \r\n",logid_2);
						String title = rs.getString("title");
						Date dtm = rs.getDate("dtm");
						java.util.Date dtm_2 = new java.util.Date(dtm.getTime());
						System.out.printf("类型:%s  记录时间:%s  内容:%s \r\n",title,dtm_2.toLocaleString(),rs.getString("content"));
					}
				}
				rs.close();//释放结果集
			} catch (SQLException e){}
			
			//关闭连接
			try
			{
				if(dbCon!=null)
					dbCon.close();
			}
			catch(SQLException e){}
		}
	}
}