JDBC学习笔记

连接数据的步骤
1.注册驱动
2.建立连接
3.创建执行ＳＱＬ的语句(Statement)
4.执行语句
5.处理执行结果(ResultSet)
6.释放资源

public static void main(String[] args) throws ClassNotFoundException, SQLException{
	
	 //1.注册驱动
	 //DriverManager.registerDriver(new com.mysql.jdbc.Driver());
	 //System.setProperty("jdbc.drivers","com.mysql.jdbc.Driver : com.oracle.jdbc.Driver");
Class.forName("com.mysql.jdbc.Driver");
	 
	 //2.建立连接
	 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/jdbc","root","");
	
	 //3.创建语句
	 Statement st = conn.createStatement();
	 
	 //4.执行语句
	 ResultSet rs = st.executeQuery("select * from user");
	 
	 //5.处理结果
	 while(rs.next()){
		 System.out.println(rs.getObject(1)+" "+rs.getObject(2));
	 }
	 
	 //6.释放连接
	 rs.close();
	 st.close();
	 conn.close();
	 
	}

建立连接的URL格式：
JDBC：子协议：子名称//主机名：端口号/数据库名？属性1=属性值&...
主机名和端口号如果是本机和默认端口号，就可也省略。

预处理执行sql

String sql = "select id,name from user where name=?";
//预处理sql,防止sql注入
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "张三");		 
//执行语句
ResultSet rs = ps.executeQuery();

插入CLOB数据

String sql = "insert into clob_test(big_text) values(?)";
		 PreparedStatement ps = conn.prepareStatement(sql);
		 File file = new File("filename");
		 Reader reader = new BufferedReader(new FileReader(file));
		 //设置大文本流
		 ps.setCharacterStream(1,reader);		 
		 //执行语句
		ps.executeUpdate();

读取CLOB数据

String sql = "select big_test from clob_test";
		 PreparedStatement ps = conn.prepareStatement(sql);
		 ResultSet rs = ps.executeQuery();
	    
		 while(rs.next()){
			 Clob clob = rs.getClob(1);
			 Reader reader = clob.getCharacterStream();
			 //reader = rs.getCharacterStream(1);
			 
			 //拿到reader就可以做一些事
		 }

可以返回主键的操作

String sql = "insert into user(name) values('aa')";
		 //可以返回主键
		 PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//根据传的不同参数,可以有不同的功能
		 ps.executeUpdate();
	    
		 ps.getGeneratedKeys();//获取主键

批处理执行

 String sql = "insert into user(name) values('aa')";
		 PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
	 
		 ps.addBatch();
		 ps.executeBatch();

可滚动的结果集

 String sql = "select * from user";
		 //创建可滚动的结果集,第二个参数是关于并发的
		 PreparedStatement ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		 ResultSet rs = null;
		 rs = ps.executeQuery();
		 
		 //绝对定位
		 rs.absolute(5);
		 if(rs.previous()){}

获取数据库的元数据(其他元数据获取同理，如ResultSet.getMetaData())

   Connection conn = null;
   DatabaseMetaData dbmd = conn.getMetaData();
   dbmd.getDatabaseProductName();//获取数据库名字

获取PreparedStatement 的元数据

 String sql = "select * from user where a=?,b=?";
		 PreparedStatement ps = conn.prepareStatement(sql);
		 ParameterMetaData pmd = ps.getParameterMetaData();
		 //获取参数个数
		 int count = pmd.getParameterCount();
		 
		 //获取第一个参数的信息,mysql没有很好实现这些接口,不同的驱动有不同的实现
		 pmd.getParameterClassName(1);//参数类名，java.lang.String
		 pmd.getParameterType(1);//参数类型,12
		 pmd.getParameterTypeName(1);//参数数据库类型名字，VARCHAR