jdbc

jdbc全称为Java Data Base Connectivity（java数据库连接）
可以为多种数据库提供统一的访问
jdbc使用
     jdbc编程步骤
        加载驱动程序：Class.forName(driverClass)
    加载Mysql驱动：Class.forName("com.mysql.jdbc.Driver");
    加载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver")
     获得数据库连接：
         DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/jdbc","root","root");
     创建Statement对象：conn.createStatement();
案例：

 package edu.hpu.study;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;


public class DBUtil {
	
		
		private final static String DRIVER = "com.mysql.jdbc.Driver";
		private final static String URL = "jdbc:mysql://localhost:3306/hello";
		private final static String PASSWORD = "";
		private final static String USER = "root";
		
		public static void main(String[] args) throws Exception {
			Class.forName(DRIVER);
			Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
           Statement st = conn.createStatement();		    
 		  ResultSet rs = st.executeQuery("select user_name,age from goddess");
 		  
 		  while(rs.next()){
 			  System.out.println(rs.getString("user_name") + "," + rs.getInt("age"));
 		  }
		}
 
}

jdbc各种连接方式的对比
     1 jdbc+odbc桥的方式
     特点：需要数据库的ODBC驱动，仅适用于微软的系统
     2 jdbc + 厂商API的形式
     特点：厂商API一般使用C编写
     3 jdbc + 厂商Database Connection
     Server + DataBase的形式
     特点： 在JAVA与DATABASE之间架起了一台专门用与数据库连接的服务器
     4 jdbc + database的连接方式
     特点：这使得Application与数据库分开
     开发者只需关心内部逻辑的实现而不需注重数据库连接的具体体现

事先先在sqlyog中新建一个数据库，然后建一个表，

然后使用myeclipse新建一个Web service project，分别建立四个包，cn.edu.hpu.service,

                 cn.edu.hpu.servlet,

                 cn.edu.hpu.test,

                 cn.edu.hpu.util,

然后在第一个包中建立一个对象（本文是建立一个简单的学生数据库），

 代码如下：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.model;

public class Student {
      private int id;
      private String name;
      private String tel;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getTel() {
		return tel;
	}
	public void setTel(String tel) {
		this.tel = tel;
	}
      
}
</span>

第二个包中有两个文件，一个为StudentManager抽象接口，另一个为StudentManagerImpl，实现StudentManager接口。

其代码如下：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.service;

import java.util.List;
import cn.edu.hpu.jdbc.model.Student;
public interface StudentManager {

    public List<Student> getStudents();
    public boolean add(Student stu);
    public boolean del(int id);
    public boolean update(Student stu);

    public Student getStudentById(int id);
}</span>

<span style="font-size:14px;">package cn.edu.hpu.jdbc.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import cn.edu.hpu.jdbc.model.Student;
import cn.edu.hpu.jdbc.util.DBOperator;


public class StudentManagerImpl implements StudentManager{
     public boolean add(Student stu){
    	 boolean flag=false;
    	 
    	 Connection conn = null;
    	 PreparedStatement pst = null;
    	 try{
    		 conn = DBOperator.getConnection();
    		 String sql = "insert into student_xinxi(name,tel) values(?,?)";
    		 pst = conn.prepareStatement(sql);
    		 pst.setString(1,stu.getName());
    		 pst.setString(2,stu.getTel());
    		 
    		 int rows = pst.executeUpdate();
    		 if(rows > 0){
                 flag = true;    			 
    		 }
    	 }catch(Exception ex){
    		 ex.printStackTrace();
         }finally{
        	 DBOperator.close(pst,conn);
         }
    	 return flag;
}
    public boolean del(int id){
    	boolean flag = false;
        Connection conn = null;
        PreparedStatement pst = null;
        
        try{
        	conn = DBOperator.getConnection();
        	String sql = "delete from student_xinxi where id=?";
        	pst = conn.prepareStatement(sql);
        	pst.setInt(1, id);
        	
        	int rows = pst.executeUpdate();
        	if(rows > 0){
        		flag=true;
        	}
        }catch(Exception ex){
        	ex.printStackTrace();
        }finally{
        	DBOperator.close(pst,conn);
        }
        return flag;
    }
	public List<Student> getStudents(){
		List<Student> list = new ArrayList<Student>();
		
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		
		try{
			conn = DBOperator.getConnection();
			String sql = "select * from student_xinxi";
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			
			while(rs.next()){
				Student stu = new Student();
				stu.setId(rs.getInt("id"));
				stu.setName(rs.getString("name"));
				stu.setTel(rs.getString("tel"));
				
				list.add(stu);
			}
		} catch(Exception ex) {
			ex.printStackTrace();
			
		}finally{
			DBOperator.close(rs,st,conn);
		}
		
		return list;
	}
    public boolean update(Student stu){
    	boolean flag = false;
    	Connection conn = null;
    	PreparedStatement pst = null;
    	
        try{
        	conn = DBOperator.getConnection();
        	String sql = "update student_xinxi set name=?,tel=? where id=?";
        	pst = conn.prepareStatement(sql);
        	pst.setString(1, stu.getName());
        	pst.setString(2, stu.getTel());
        	pst.setInt(3,stu.getId());
        	
        	int rows = pst.executeUpdate();
        	if(rows >0){
        		flag=true;
        	}
        } catch(Exception ex){
        	ex.printStackTrace();
        	
        } finally{
        	DBOperator.close(pst,conn);
        } 
        return flag;
    }		
    public Student getStudentById(int id){
    	Student stu = new Student();
    	
    	Connection conn = null;
    	Statement st = null;
    	ResultSet rs = null;
    	
    	try {
    		conn = DBOperator.getConnection();
    		String sql ="select * from student_xinxi where id=" + id;
    		st = conn.createStatement();
    		rs = st.executeQuery(sql);
    		if(rs.next()){
    			stu.setId(rs.getInt("id"));
    			stu.setName(rs.getString("name"));
    			stu.setTel(rs.getString("tel"));
    		    }
    	    } catch (Exception ex){
    	    	ex.printStackTrace();
               DBOperator.close(rs,st,conn);
    	    }
    	return stu;
    }
}</span>

第三个包中为测试所写的，

其测试方法如下：

添加测试：

  

<span style="font-size:14px;">package cn.edu.hpu.jdbc.test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class AddTest {
	public static void main(String[] args) throws Exception {
   		
    Class.forName("com.mysql.jdbc.Driver");
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","123");
    
    String sql = "insert into student_xinxi(name,tel) values(?,?)";
    PreparedStatement pst = conn.prepareStatement(sql);
    pst.setString(1,"你好");
    pst.setString(2,"你吃饭了么");
    pst.executeUpdate();
    System.out.println("恭喜存储成功！");
	}
}
</span>

删除测试：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.test;

import cn.edu.hpu.jdbc.service.StudentManager;
import cn.edu.hpu.jdbc.service.StudentManagerImpl;

public class DelTest {
	public static void main(String[] args) {
       StudentManager dao = new StudentManagerImpl();
       boolean flag = dao.del(2);
       if(flag){
          System.out.println("成功！");
       }else{
    	   
    	   System.out.println("失败！");
       }
}
}
</span>

连接测试：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.test;

import java.sql.Connection;

import cn.edu.hpu.jdbc.util.DBOperator;

public class ConnectionTest {
	public static void main(String[] args) {
		
     Connection conn = DBOperator.getConnection();
     if(conn != null)
    	System.out.println("恭喜连接成功！！");
   
	}    	 
}
    	</span>

更新测试：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.test;

import cn.edu.hpu.jdbc.model.Student;
import cn.edu.hpu.jdbc.service.StudentManager;
import cn.edu.hpu.jdbc.service.StudentManagerImpl;

public class UpdateTest {
      public static void main(String[] args) {
		StudentManager dao = new StudentManagerImpl();
		Student stu = dao.getStudentById(1);
		stu.setName("大厦");
		stu.setTel("99991");
		boolean flag = dao.update(stu);
		if(flag)
			System.out.println("成功！");
		else
			System.out.println("失败！");
	}
}</span>

获取数据测试：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.test;

import cn.edu.hpu.jdbc.model.Student;
import cn.edu.hpu.jdbc.service.StudentManager;
import cn.edu.hpu.jdbc.service.StudentManagerImpl;

public class GetTest {
      public static void main(String[] args) {
		StudentManager dao = new StudentManagerImpl();
		Student stu = dao.getStudentById(3);
		if(stu != null){
			System.out.println("成功！");
			System.out.println("id:" + stu.getId());
			System.out.println("名字：" + stu.getName());
			System.out.println("电话：" + stu.getTel());
		}
	}
}
</span>

接下来是最重要的一步，也就是连接SQLyog，这是最重要的一步，我是将本文件建立在最后一个包中的，

代码如下：

<span style="font-size:14px;">package cn.edu.hpu.jdbc.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class DBOperator {
    private final static String driver = "com.mysql.jdbc.Driver";
    private final static String url = "jdbc:mysql://localhost:3306/student";//最后一个需要改变，跟你所建的数据库的名字相同
static{
	try{
		Class.forName(driver);
	 }   catch (ClassNotFoundException e){
		e.printStackTrace();
	 }
}
public static Connection getConnection(){
           Connection conn = null;
      try {
    	  conn = DriverManager.getConnection(url,"root","123");//分别为路径，用户名，密码，有可能不一样
      }catch (SQLException e){
    	  e.printStackTrace();
      }
      return conn;
}
public static void close(ResultSet rs, Statement st, Connection conn){
	try{
		if(rs != null){
		    rs.close();
		}
		if(st != null){
			st.close();
		}
		if(conn != null){
			conn.close();
		}
	}   catch(Exception ex){
	  ex.printStackTrace();
 }
}
	public static void close(Statement st, Connection conn){
		close(null,st,conn);
	}
}
</span>

之前一直都搞不懂这个问题，现在懂了