jdbc

工程中的包


1.包的命名规则
com.公司名.(项目名.具体模块的名称）实现分类的名称

1.po包：存放的是javabean类，每个Javabean类对应数据库中一张表。类名和表名一致

2.dao包：存放的是操作数据的类，即对数据库中的表进行增删查改操作的类，每个dao类对应数据库中的一张表。类
名为一表名+Dao
3.util包：存放的是公用的类和常量类
4.test包：存放的是测试类


javabean类：储存数据的类。只包括属性、构造器、setter和getter方法


数据库客服端操作数据库的步骤：
        1.连接数据库（ip地址，端口号，用户名，密码，数据库名）
2.打开发送和执行sql语句的窗口
3.发送和执行sql语句
4.根据返回结果，判断是否正确操作了数据库
5.关闭打开的所有的东西


JDBC操作数据库的步骤：
1.连接到数据库：
（1）加载数据库驱动：会抛出一个ClassNotFoundException异常
mysql:Class.forName（"com.mysql.jdbc.Driver"）
oracle：Class.forName("");


（2）利用驱动和三个参数（url,user,password）获得连接：会抛出一个SQLException异常
java.sql.Connection con = java.sql.DriverManager.getConnection(url,user,password);
mysql:String url = "jdbc:mysql://127.0.0.1:端口号/databaseName".
oracle:String url = "";


3.通过连接获得操作数据库的对象：会抛出一个SQLException异常
java.sql.Statement st = con.createStatement();


4.使用java.sql.Statement对象执行sql语句：会抛出一个SQLException异常
执行更新sql语句：int rows = st.executeUpdate(sql);
执行查询sql语句：java.sql.ResultSet re = st.executeQuery(sql);


5.根据返回的rows/re，判断SQL语句是否正确执行：
rows!=0 SQL语句正常执行
 re.next()==true  表示查出了字表


6.关闭，先打开后关闭
关闭re:re.close():会抛出一个SQLException异常
 关闭st:st.close():会抛出一个SQLException异常
 关闭con:con.close()会抛出一个SQLException异常


第一个jar 包：数据库的驱动
Web工程的jar包都是放在WebRoot/WEB-INF/lib文件夹里（放在里面，自动导入）


几个接口：
java.sql.Connection是一个接口
java.sql.Statement是一个接口
java.sql.ResultSet是一个接口
java.sql.PreparedStatement是一个接口


数据库连接配置文件：database.properties只加载一次，放在src文件夹下面
properties文件以键（name）值（value）方式保存数据
从properties文件中解析出value值：
InputStream is = null;
  is = DBConnectionUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
  Properties p = new Properties();
  p.load(is); 
  String value = p.getProperty("name");


java.sql.Statement和java.sql.PreparedStatement比较
代码比较
1.SQL的写法：
Satement：String sql = "selete id from user where email =' "+email+" ' ";
PreparedStatement：String sql = "selete id from user where email = ?"
2.st/ps的获取：
Statement：st = con.createStatement();
PreparedStatement：ps = con.preparedStatement(sql);
3.参数的设置：
Statement：在SQL中给出
PreparedStatement：ps.setType(1,value);  ps.setType(2,value);
4.执行SQL：
Statement：int rows  =  st.executeUpdate(sql);      re = st.executeQuery(sql);
PreparedStatement：int rows = ps.executeUpdate();    re = ps.executeQuery();


PreparedStatement的优势：
1.防止SQL的注入，提高安全性
2.对参数实现类型的自动转换，代码的可读性，可维护性提高

3,.对于批处理：PreparedStatement效率更高一些（oracle明显，MySQL区别不大）

代码笔记：

工具包：

package com . yy . jdbc . util ; import java.io.IOException ; import java.io.InputStream ; import java.sql.Connection ; import java.sql.DriverManager ; import java.sql.ResultSet ; import java.sql.SQLException ; import java.sql.Statement ; import java.util.Properties ; /** * 这个是工具类 * @author 秦博 * */ public class DBconnectionUtil { private static String jdbcDriver = "" ; private static String jdbcURL = "" ; private static String jdbcUser = "" ; private static String jdbcPassword = "" ; static { InputStream is = DBconnectionUtil . class . getClassLoader (). getResourceAsStream ( "jdbc.properties" ); Properties p = new Properties (); try { p . load ( is ); jdbcDriver = p . getProperty ( "jdbcDriver" ); jdbcURL = p . getProperty ( "jdbcURL" ); jdbcUser = p . getProperty ( "jdbcUser" ); jdbcPassword = p . getProperty ( "jdbcPassword" ); is . close (); } catch ( IOException e ) { // TODO Auto-generated catch block e . printStackTrace (); } } public static Connection getConnection (){ Connection con = null ; try { Class . forName ( jdbcDriver ); con = DriverManager . getConnection ( jdbcURL , jdbcUser , jdbcPassword ); } catch ( ClassNotFoundException e ) { e . printStackTrace (); } catch ( SQLException e ) { e . printStackTrace (); } return con ; } /*关闭链接的方法*/ public static void isClose ( ResultSet set , Statement st , Connection con ){ try { if ( set != null ){ set . close (); } } catch ( SQLException e ) { // TODO Auto-generated catch block e . printStackTrace (); } finally { try { if ( st != null ){ st . close (); } } catch ( SQLException e ) { // TODO Auto-generated catch block e . printStackTrace (); } finally { try { if ( con != null ){ con . close (); } } catch ( SQLException e ) { // TODO Auto-generated catch block e . printStackTrace (); } } } } }

 来自CODE的代码片

DBconnectionUtil.java

方法类：

package com . yy . jdbc . dao ; import java.sql.Connection ; import java.sql.PreparedStatement ; import java.sql.ResultSet ; import java.sql.SQLException ; import java.sql.Statement ; import java.util.ArrayList ; import com.yy.jdbc.po.User ; import com.yy.jdbc.util.DBconnectionUtil ; public class UserDao { private String jdbcDriver = "com.mysql.jdbc.Driver" ; private String jdbcURL = "jdbc:mysql://localhost:3306/jdbc?characterEncoding=UTF-8" ; private String jdbcUser = "root" ; private String jdbcPassword = "" ; Connection con = null ; Statement st = null ; ResultSet set = null ; PreparedStatement ps = null ; /*防止注入预执行语句*/ /** * * @param user * @return */ private static UserDao userDao = new UserDao (); public static UserDao getInstance (){ return userDao ; } public boolean saveUser ( User user ) { boolean flag = false ; con = DBconnectionUtil . getConnection (); /*创建SQL语句*/ String sql = "insert into user(username,sex,password) value ('" + user . getUsername () + "'," + user . getSex () + "," + user . getPassword ()+ ")" ; try { st = con . createStatement (); /*执行SQLbig查看返回结果*/ int rows = st . executeUpdate ( sql ); if ( rows != 0 ){ flag = true ; } } catch ( SQLException e ) { e . printStackTrace (); } finally { /*调用关闭链接的方法*/ DBconnectionUtil . isClose ( set , st , con ); } return flag ; } public boolean deleteById ( int userId ){ boolean flag = false ; con = DBconnectionUtil . getConnection (); /*创建SQL语句*/ //String sql ="delete from user where userId="+userId; String sql = "delete from user where userId= ?" ; try { /*创建执行语句*/ //st =con.createStatement(); ps = con . prepareStatement ( sql ); ps . setInt ( 1 , userId ); /*执行SQLbig查看返回结果*/ //int rows= st.executeUpdate(sql); int rows = ps . executeUpdate (); if ( rows != 0 ){ flag = true ; } } catch ( SQLException e ) { e . printStackTrace (); } finally { /*调用关闭链接的方法*/ DBconnectionUtil . isClose ( set , st , con ); } return false ; } public boolean updata ( String oldname , String newname ){ boolean flag = false ; con = DBconnectionUtil . getConnection (); /*创建SQL语句*/ String sql = "select * from user" ; try { /*创建执行语句*/ st = con . createStatement (); /*执行SQLbig查看返回结果*/ int rows = st . executeUpdate ( sql ); if ( rows != 0 ){ flag = true ; } } catch ( SQLException e ) { // TODO Auto-generated catch block e . printStackTrace (); } finally { /*调用关闭链接的方法*/ DBconnectionUtil . isClose ( set , st , con ); } return false ; } public ArrayList < User > select (){ boolean flag = false ; ArrayList < User > list = new ArrayList < User >(); con = DBconnectionUtil . getConnection (); /*创建SQL语句*/ String sql = "select * from user" ; System . out . println ( sql ); try { /*创建执行语句*/ st = con . createStatement (); /*执行SQLbig查看返回结果*/ set = st . executeQuery ( sql ); while ( set . next ()){ User user = new User (); user . setUserId ( set . getInt ( "userId" )); user . setUsername ( set . getString ( "username" )); user . setSex ( set . getByte ( "sex" )); user . setPassword ( set . getString ( "password" )); user . setFlag ( set . getInt ( "flag" )); list . add ( user ); } } catch ( SQLException e ) { e . printStackTrace (); } finally { /*调用关闭链接的方法*/ DBconnectionUtil . isClose ( set , st , con ); } return list ; } public ArrayList < User > selectLimit ( int page , int index ){ boolean flag = false ; ArrayList < User > list = new ArrayList < User >(); con = DBconnectionUtil . getConnection (); /*创建SQL语句*/ String sql = "select * from user order by userId asc limit ?,?" ; System . out . println ( sql ); try { /*创建执行语句*/ ps = con . prepareStatement ( sql ); ps . setInt ( 1 , page ); ps . setInt ( 2 , index ); set = ps . executeQuery (); /*执行SQLbig查看返回结果*/ while ( set . next ()){ User user = new User (); user . setUserId ( set . getInt ( "userId" )); user . setUsername ( set . getString ( "username" )); user . setSex ( set . getByte ( "sex" )); user . setPassword ( set . getString ( "password" )); user . setFlag ( set . getInt ( "flag" )); list . add ( user ); } } catch ( SQLException e ) { e . printStackTrace (); } finally { /*调用关闭链接的方法*/ DBconnectionUtil . isClose ( set , st , con ); } return list ; } }

 来自CODE的代码片

UserDao.java

sql表中对应的user类：

package com . yy . jdbc . po ; /** * user这个表类 * @author 秦博 * */ public class User { private int userId ; private String username ; private byte sex ; private String password ; private int flag ; public User () { // TODO Auto-generated constructor stub } public User ( int userId , String username , byte sex , String password , int flag ) { super (); this . userId = userId ; this . username = username ; this . sex = sex ; this . password = password ; this . flag = flag ; } public int getUserId () { return userId ; } public void setUserId ( int userId ) { this . userId = userId ; } public String getUsername () { return username ; } public void setUsername ( String username ) { this . username = username ; } public byte getSex () { return sex ; } public void setSex ( byte sex ) { this . sex = sex ; } public String getPassword () { return password ; } public void setPassword ( String password ) { this . password = password ; } public int getFlag () { return flag ; } public void setFlag ( int flag ) { this . flag = flag ; } }

package com . yy . jdbc . test ; import java.util.ArrayList ; import com.yy.jdbc.dao.UserDao ; import com.yy.jdbc.po.User ; import com.yy.jdbc.util.DBconnectionUtil ; public class Test { @org.junit.Test public void testInsert () throws Exception { User user = new User ( 0 , "tim" , ( byte ) 0 , "55655" , 0 ); UserDao dao = new UserDao (); System . out . println ( dao . saveUser ( user )); UserDao . getInstance (). saveUser ( user ); } @org.junit.Test public void testDeleft () throws Exception { UserDao . getInstance (). deleteById ( 9 ); } @org.junit.Test public void testUpdata () throws Exception { UserDao . getInstance (). updata ( "qb" , "qqbb" ); } @org.junit.Test public void testSelect () throws Exception { ArrayList < User > list = UserDao . getInstance (). select (); for ( User li: list ){ System . out . println ( li . getUserId ()+ " " + li . getUsername ()+ " " + li . getPassword ()+ " " + li . getSex ()+ " " + li . getFlag ()); } } @org.junit.Test public void testLimit () throws Exception { ArrayList < User > list = UserDao . getInstance (). selectLimit ( 1 , 5 ); for ( User li: list ){ System . out . println ( li . getUserId ()+ " " + li . getUsername ()+ " " + li . getPassword ()+ " " + li . getSex ()+ " " + li . getFlag ()); } } }