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.Driver
jdbcURL=jdbc\:mysql\://localhost\:3306/jdbc?characterEncoding\=UTF-8
jdbcUser=root
jdbcPassword=

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 {

	@Test
	public 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);
		};
	}
	@Test
	public void testDeleteUser() throws Exception {
		UserDao.getInstance().deleteUserByUserId(1);
	}
	@Test
	public void testupdate() throws Exception {
		UserDao.getInstance().updateUserByUsername("小明","222222");
	}
	@Test
	public void testGetAllUserList() throws Exception {
		List
     
     
      
       userList = UserDao.getInstance().getAllUserList();
		for (User user : userList) {
			System.out.println(user.getUsername());
		}
	}
	@Test
	public void testGetUserListPage() throws Exception {
		List
      
      
       
        userList = UserDao.getInstance().getUserListPage(10, 10);
		for (User user : userList) {
			System.out.println(user.getUserId());
		}
	}
}

      
      
     
     

欢迎大家评论和指正！