Java连接数据库

工程建项目和建包命名规范：

包的命名规则：

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

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


javabean类：储存数据的类。只包括属性、构造器、setter和getter方法 快捷键alt+shift+s


数据库客服端操作数据库的步骤：
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.关闭，先打开后关闭有close();方法的都要关闭
关闭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区别不大）

下面是用java代码连接数据库的代码：

在数据库先建立一个user表

package com.yy.jdbc.po;public class User {//数据库中表中的五个字段名private int userId;private String username;private int sex;private String password;private int flag;//下面是getters和setters方法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 int getSex() {return sex;}public void setSex(int 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.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 java.util.List;import com.yy.jdbc.po.User;import com.yy.jdbc.util.DBconnectionUitl; class UserDao {//单例模式public UserDao(){}private static UserDao userDao = new UserDao();public static UserDao getInstance(){return userDao;}/** * @param user *  * @return true保存成功,false保存失败 */public boolean saveUser(User user){boolean flag = false;Connection conn = null;Statement st = null;String sql = "";try {//获取连接conn = DBconnectionUitl.getConnection();//产生执行sql语句的对象st = conn.createStatement();//拼凑sql语句sql = "insert into user(username,sex,password) values ('" + user.getUsername() + "',"+ user.getSex() + ",'"+ user.getPassword() + "'"+ ")";System.out.println(sql);//执行sql语句int rows = st.executeUpdate(sql);if(rows != 0)flag = true;} catch (SQLException e) {e.printStackTrace();} finally {DBconnectionUitl.closeAll(null, st, conn);}return flag;}/** * 通过userId删除user对象 * @param userId * @return */public boolean deleteUserByUserId(int userId){boolean flag = false;Connection conn = null;Statement st = null;String sql = "delete from user where userId = " + userId;try {conn = DBconnectionUitl.getConnection();st = conn.createStatement();int rows = st.executeUpdate(sql);if(rows != 0)flag = true;} catch (SQLException e) {e.printStackTrace();} finally {DBconnectionUitl.closeAll(null, st, conn);}return flag;}/** * 通过username修改user对象 * @param username * @return */public boolean updateUserByUsername(String username,String password){boolean flag = false;Connection conn = null;Statement st = null;String sql = "update user set password='"+password+"' where username = '" + username+"'";System.out.println(sql);try {conn = DBconnectionUitl.getConnection();st = conn.createStatement();int rows = st.executeUpdate(sql);if(rows != 0)flag = true;} catch (SQLException e) {e.printStackTrace();} finally {DBconnectionUitl.closeAll(null, st, conn);}return flag;}/** * 查询user表 * @return */public List getAllUserList(){List userList = new ArrayList();Connection conn = null;Statement st = null;String sql = "select * from user";ResultSet rs = null;try {conn = DBconnectionUitl.getConnection();st = conn.createStatement();rs = st.executeQuery(sql);while(rs.next()){User user = new User();user.setUserId(rs.getInt("userId"));user.setUsername(rs.getString("username"));user.setSex(rs.getInt("sex"));user.setPassword(rs.getString("password"));user.setFlag(rs.getInt("flag"));userList.add(user);}} catch (SQLException e) {e.printStackTrace();} finally {DBconnectionUitl.closeAll(rs, st, conn);}return userList;}/** * 分页查询表中的数据  关键字limit * @param start * @param pageNo * @return */public List getUserListPage(int start,int pageNo){List userList = new ArrayList();Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;String sql = "select * from user order by userId asc limit ?,?";try {conn = DBconnectionUitl.getConnection();ps = conn.prepareStatement(sql);ps.setInt(1, start);ps.setInt(2, pageNo);rs = ps.executeQuery();while(rs.next()){User user = new User();user.setUserId(rs.getInt("userId"));user.setUsername(rs.getString("username"));user.setSex(rs.getInt("sex"));user.setPassword(rs.getString("password"));user.setFlag(rs.getInt("flag"));userList.add(user);}} catch (SQLException e) {e.printStackTrace();} finally {DBconnectionUitl.closeAll(rs, ps, conn);}return userList;}}

package com.yy.servlet.util;import java.io.IOException;import java.io.InputStream;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Properties;/** *  * @author Administrator *四个参数 */public class DBconnectionUitl {private static String jdbcDriver = "";//驱动private static String jdbcURL = "";//url地址private static String jdbcUser = "";//用户名private static String jdbcPassword = "";//密码//上面四个参数写进jdbc.properties中 然后在通过流的形式读取static {InputStream is = null;Properties p = new Properties();try {is = DBconnectionUitl.class.getClassLoader().getResourceAsStream("jdbc.properties");p.load(is);jdbcDriver = p.getProperty("jdbcDriver");jdbcURL = p.getProperty("jdbcURL");jdbcUser = p.getProperty("jdbcUser");jdbcPassword = p.getProperty("jdbcPassword");} catch (IOException e) {e.printStackTrace();}}public static Connection getConnection(){Connection conn = null;try {//加载驱动Class.forName(jdbcDriver);//获取连接conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcPassword);} catch (ClassNotFoundException e) {e.printStackTrace();} catch (SQLException e) {e.printStackTrace();}return conn;}//将要关闭的方法封装 写在一起public static void closeAll(ResultSet rs,Statement st,Connection conn){try {if(rs != null)rs.close();} catch (Exception e) {e.printStackTrace();} finally {try {if(st != null)st.close();} catch (Exception e) {e.printStackTrace();} finally {try {if(conn != null)conn.close();} catch (Exception e) {e.printStackTrace();}}}}public static void closeAll(ResultSet rs,PreparedStatement ps,Connection conn){try {if(rs != null)rs.close();} catch (Exception e) {e.printStackTrace();} finally {try {if(ps != null)ps.close();} catch (Exception e) {e.printStackTrace();} finally {try {if(conn != null)conn.close();} catch (Exception e) {e.printStackTrace();}}}}//在main函数中打印输出数据库是否连接成功public static void main(String[] args) {System.out.println(getConnection());}}

jdbcDriver=com.mysql.jdbc.DriverjdbcURL=jdbc\:mysql\://localhost\:3306/jdbc?characterEncoding\=UTF-8jdbcUser=rootjdbcPassword=

package com.yy.jdbc.test;import java.util.List;import org.junit.Test;import com.yy.jdbc.dao.UserDao;import com.yy.jdbc.po.User;public class UserDaoTest {@Testpublic void testSaveUser() throws Exception {User user = new User();user.setUsername("小红"+(int)(Math.random()*1000));user.setSex(1);user.setPassword("zxcvb");for (int i = 0; i < 100; i++) {UserDao.getInstance().saveUser(user);};}@Testpublic void testDeleteUser() throws Exception {UserDao.getInstance().deleteUserByUserId(1);}@Testpublic void testupdate() throws Exception {UserDao.getInstance().updateUserByUsername("小明","222222");}@Testpublic void testGetAllUserList() throws Exception {List userList = UserDao.getInstance().getAllUserList();for (User user : userList) {System.out.println(user.getUsername());}}@Testpublic void testGetUserListPage() throws Exception {List userList = UserDao.getInstance().getUserListPage(10, 10);for (User user : userList) {System.out.println(user.getUserId());}}}

欢迎大家评论和指正！