JDBC 连接 Oracle/MySql 数据库 不定期 小栗子...

于 2015-09-05 22:44:43 发布
阅读量1.2k 收藏 13
点赞数 2
分类专栏： JDBC 文章标签： 数据库 jdbc
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Simba_cheng/article/details/48231167
版权

好久没有写JDBC了，差不多都忘光了，趁着放假稍微复习了一下...写个比较经典的JDBC连接方法...

package com.jdbc.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBUtil {
	public static void main(String[] args) throws Exception {
		
		//1.加载驱动(注册)
		Class.forName("oracle.jdbc.driver.OracleDriver");
		//2.建立连接(连接数据库)
		Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","1234");
		//3.通过连接创建语句对象
		Statement state = conn.createStatement();
		String sql = "select * from emp";
		
		//4.执行SQL、接收返回数据
		ResultSet res = state.executeQuery(sql);
		
		while(res.next()){
//			int id = res.getInt(1);
//			String name = res.getString(2);
//			String job = res.getString(3);
//			int mgr = res.getInt(4);
//			String hirdate = res.getString(5);
//			String sal = res.getString(6);
//			String comm = res.getString(7);
//			String deptno = res.getString(8);
			String id = res.getString("empno");
			String name = res.getString("ename");
			String job = res.getString("job");
			String mgr = res.getString("mgr");
			String hirdate = res.getString("hirdate");
			String sal = res.getString("sal");
			String comm = res.getString("comm");
			String deptno = res.getString("deptno");
			
			System.out.println(id+","+name+","+job+","+mgr+","+hirdate+","+sal+","+comm+","+deptno);
		}
		res.close();
		state.close();
		conn.close();
	}
}


上面那个是最普通的JDBC的写法...下面来一个稍微文艺点的... 


package com.jdbc.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBUtil2 {
	public static void main(String[] args) {

		Connection conn = null;
		Statement state = null;
		ResultSet res = null;

		String sql = "select * from emp";

		try{

			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","system","1234");
			state = conn.createStatement();
			res = state.executeQuery(sql);

			while(res.next()){
				String id = res.getString("empno");
				String name = res.getString("ename");
				String job = res.getString("job");
				String mgr = res.getString("mgr");
				String hirdate = res.getString("hirdate");
				String sal = res.getString("sal");
				String comm = res.getString("comm");
				String deptno = res.getString("deptno");

				System.out.println(id+","+name+","+job+","+mgr+","+hirdate+","+sal+","+comm+","+deptno);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{					//关闭
			try{
				if(res != null){
					res.close();
					res = null;		//设置为null，方便垃圾收集器随时将其回收(关系不太大)
				}
				if(state != null){
					state.close();
					state = null;
				}
				if(conn != null){
					conn.close();
					conn = null;
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		}
	}
}

全文完.....


添加于2015-12-10

感觉自己还是没什么长进啊...

MySql 版本

package com.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * 测试练习JDBC-MySql
 * @author cyx
 *
 */
public class JDBC_Test {

	public static void main(String[] args) {		
		String url = "jdbc:mysql://localhost:3306/jdbc_test";	//连接数据库的URL		
		String username = "root";								//数据库的用户名		
		String password = "1234";								//数据库的密码		
		try{
			//1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建数据库连接对象
			Connection conn = DriverManager.getConnection(url,username,password);
			//3.创建Statement对象
			Statement state = conn.createStatement();
			//4.获取ResultSet对象
			String sql = "SELECT * FROM TEST1";
			ResultSet res = state.executeQuery(sql);			
			while(res.next()){
				String id = res.getString("id");
				String user = res.getString("username");
				String pw = res.getString("password");
				System.out.println(id+" "+user+" "+pw);
			}
			//5.关闭数据库连接
			res.close();
			state.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}		
	}
	
}

版本2 

package com.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.jdbc.entity.User;


public class JDBC_Test2 {
	private String url = "jdbc:mysql://localhost:3306/jdbc_test";
	private String username = "root";
	private String password = "1234";

	public static void main(String[] args) {
		JDBC_Test2 jt = new JDBC_Test2();
		List<User> list = jt.findAll();
		for (User user : list) {
			System.out.println(user);
		}
	}

	public List<User> findAll(){
		List<User> list = new ArrayList<User>();
		try{			
			String sql = "SELECT * FROM TEST1";
			//1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建数据库连接对象
			Connection conn = DriverManager.getConnection(url,username,password);
			//3.创建Statement对象
			Statement state = conn.createStatement();
			ResultSet res = state.executeQuery(sql);
			while(res.next()){
				User user = new User();
				user.setId(res.getString("id"));
				user.setUsername(res.getString("username"));
				user.setPassword(res.getString("password"));
				list.add(user);
			}
			res.close();
			state.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return list;	
	}

}

版本3 

package com.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import com.jdbc.entity.User;

public class JDBC_Test3 {	
	public static void main(String[] args) {
		JDBC_Test3 jt = new JDBC_Test3();
		User user = new User();
		user.setId("1");
		user.setUsername("ycy");
		user.setPassword("1234");
		jt.insert(user);
	}
	
	public void insert(User user){		
		String sql = "INSERT INTO TEST1 VALUES("+user.getId()+",'"+user.getUsername()+"','"+user.getPassword()+"')";
		Statement state = getStatement();
		try{
			int result = state.executeUpdate(sql);
			if(result < 0){
				throw new Exception("插入数据库错误..");
			}else{
				System.out.println("插入了"+result+" 条数据");
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	
	
	public Statement getStatement(){
		String url = "jdbc:mysql://localhost:3306/jdbc_test";
		String username = "root";
		String password ="1234";		
		Statement state = null;
		try{
			//1.加载驱动
			Class.forName("com.mysql.jdbc.Driver");
			//2.创建数据库连接对象
			Connection conn = DriverManager.getConnection(url,username,password);
			//3.创建Statement对象
			state = conn.createStatement();					
		}catch(Exception e){
			e.printStackTrace();
		}		
		return state;
	}	
}



package com.jdbc.test;

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

public class JDBC_Test4 {
	public static void main(String[] args) throws Exception {
		JDBC_Test4 jt = new JDBC_Test4();
		jt.Login("cyx", "123456");
	}
	
	public void Login(String username ,String password) throws Exception{		
		String url = "jdbc:mysql://localhost:3306/jdbc_test";
		String un = "root";
		String pw ="1234";
		
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		try{			
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url,un,pw);
			//设置默认提交为false
			conn.setAutoCommit(false);
			String sql = "SELECT USERNAME,PASSWORD FROM TEST1 WHERE USERNAME = ? AND PASSWORD = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, username);
			pst.setString(2, password);
			System.out.println(pst.toString());
			rs = pst.executeQuery();
			if(rs.next()){
				System.out.println("登陆成功");
			}else{
				System.out.println("登录失败");
			}
			conn.commit();
		}catch(Exception e){
			conn.rollback();
			e.printStackTrace();
		}finally{			
			if(rs != null){
				rs.close();
			}
			if(pst != null){
				pst.close();
			}
			if(conn != null){
				conn.close();
			}
		}
		
	}
}


//更新与2015-12-13

封装了一个Connection,使用链接时候直接获取,不用再写好几遍了....

package com.struts_extjs_test.utils;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbcp.BasicDataSourceFactory;

public class JDBC {
	
	private static Properties p = null;
	private static DataSource ds = null;
	static{
		//解析properties文件
		p = new Properties();
		InputStream in = JDBC.class.getClassLoader().getResourceAsStream("MySqlDb.properties");
		try{
			p.load(in);
			ds = BasicDataSourceFactory.createDataSource(p);
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws Exception{
		Connection conn = ds.getConnection();
		return conn;
	}
	
	public static void close(ResultSet rs ,PreparedStatement stmt, Connection conn){
		try{
			if(rs != null && !rs.isClosed()){
				rs.close();
			}
			if(stmt != null && !stmt.isClosed()){
				stmt.close();
			}
			if(conn != null && !conn.isClosed()){
				conn.close();
			}
		}catch(Exception e){
			e.printStackTrace();
		}
	}
}



Simba_cheng
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值