java_jdbc基础笔记

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

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

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

4.test包：存放的是测试类

数据库客服端操作数据库的步骤：

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异常

连接使用SQL的方法（笔记）：

package com.yy.jdbc.po;

public class User {
	private int userId; 
	private String username;
	private int sex;
	private String password;
	private int 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 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.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.jsp.jstl.sql.SQLExecutionTag;
import javax.swing.JButton;

import sun.awt.geom.AreaOp.IntOp;

import com.yy.jdbc.po.User;

public class UserDao {
	private String jdbcDriver ="com.mysql.jdbc.Driver";
	private String jdbcURL = "jdbc:mysql://localhost:3306/User?characterEncoding=UTF-8";
	private String jdbcUser = "root";
	private String jdbcpassword = "";
	
	
	
	
	public UserDao(){
	}
	private static UserDao userDao = new UserDao();
	public static UserDao getInstance(){
		return userDao;
	}
	public boolean savaUser(User user){
		boolean flag = false;
		//加载驱动
		
		Connection conn = null;
		Statement st = null;
		try{
			Class.forName(jdbcDriver);
			//通过URL  数据库   数据库密码 连接
			conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcpassword);
			//产生执行sql语句的对象
			st = conn.createStatement();
			String sql ="";
			//拼凑SQL语句
			sql = "insert into user(username,sex,password) values('"
				+ user.getUsername()+ "',"
				+ user.getSex() + ",'"
				+ user.getPassword()+"')";
				
			System.out.println(sql);
			
			int rows = st.executeUpdate(sql);
			if(rows !=0)
				flag = true;
		}catch (ClassNotFoundException e) {
			
			e.printStackTrace();
	
		} catch (SQLException 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();
				}
			}
		}
		
		return flag;
	}
	/**
	 * 删除语句
	 */
	
	public boolean deleteUserByUserId(int userId){
		boolean flag = true;
		
		Connection conn = null;
		Statement st = null;
		String sql = "delete from user where userId =" + userId ;
	
		try{
			Class.forName(jdbcDriver);
			
			conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcpassword);
			st = conn.createStatement();
			int rows = st.executeUpdate(sql);
			if(rows != 0)
				flag = true;
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
			
		} catch (SQLException 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();
				}
			}
		}
		
		
		
		
		
		return flag;
	}
	
	/**
	 * 修改语句
	 */
	
	public boolean updateUserByUsername(String username,String password){
		boolean flag = true;
		Connection conn =null;
		Statement st =null;
		String sql = "update user set password='"+password+"' where username = '" + username+"'";
		
		try{
			Class.forName(jdbcDriver);
			conn = DriverManager.getConnection(jdbcURL, jdbcUser, jdbcpassword);
			
			st = conn.createStatement();
			int rows = st.executeUpdate(sql);
			if(rows != 0)
				flag = true;
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException 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();
				}
			}
		}
		
		return flag;
	}

}

package com.yy.jdbc.test;


import static org.junit.Assert.*;

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("唐");
	user.setSex(1);
	user.setPassword("123123");
	
	UserDao.getInstance().savaUser(user);
	
	}
	@Test
	public void testDeleteUser() throws Exception{
		UserDao.getInstance().deleteUserByUserId(12);
	}
	@Test
	public void testupdate() throws Exception{
		UserDao.getInstance().updateUserByUsername("唐","222222");
	}

}