JDBC--java链接数据库并对其进行操作

使用Java连接服务器的思路：

一.导入数据库的jar包：如：mysql-connector-java-5.1.0-bin.jar

二.通过反射链接加载JDBC驱动：Class.forName(驱动);

三.链接数据库：DriverManager.getConnection(数据库地址,用户名,密码);

四.通过Properties工具和输入流InputStream读取配置文件；

五.在java中通过Mysql语句进行对数据库的操作：
1、添加数据：insert into
2、删除数据：delete
3、修改数据：update
3、查询数据：selecte

MySQL语句：

• show databases—-显示所有库
• use 库名—-使用库
• show tables—-显示所有表
• desc 表名—-查询表
• insert into 表名 （字段名）values(字段的值) —-给表的字段赋值
• update 表名set 字段名=? where 字段名=?—-修改表的某个字段的值
• delete from 表名 where 字段名=?—-通过字段名删除表的某条数据
• select *from 表名—-查看表的所有信息

• select *from 表名 where 字段名=?—-通过字段名查看某条数据

  Dao包

  1.BaseDao.java

package com.bdqn.dao;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
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;
import com.bdqn.utils.Contants;

public class BaseDao {

    private static Connection conn=null;
    private static Statement st=null;
    private static ResultSet rs=null;
    private static PreparedStatement ps=null;
    static Properties pro=new Properties();

    public static Connection getConnection(){

        try {
            //加载驱动
            Class.forName(Contants.driver);
            //连接数据库
            conn=DriverManager.getConnection(Contants.url, Contants.user, Contants.pwd);
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        return conn;
    }


    //静态块，自动读取配置文件
    static{
        into();
    }


    //Properties读取配置文件专用-- 读取方法一
    /*public static void into(){
        InputStream is=null;
        //Properties是读取配置文件专用
        Properties pro=new Properties();
        try {
            //得到文件的路径
            String fileName="database.properties";
            //文件加载到流
            is=BaseDao.class.getClassLoader().getResourceAsStream(fileName);
            //读取流里配置信息
            pro.load(is);
            Contants.driver=pro.getProperty("driver");
            Contants.url=pro.getProperty("url");
            Contants.user=pro.getProperty("user");
            Contants.pwd=pro.getProperty("password");
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            try {
                if(is!=null){
                    is.close();
                }
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }

    }*/


    //Properties读取配置文件专用 --读取方法二
    public static void into(){

        try {
            InputStream is= new FileInputStream("src/database.properties");
            pro.load(is);
            Contants.driver=pro.getProperty("driver");
            Contants.url=pro.getProperty("url");
            Contants.user=pro.getProperty("user");
            Contants.pwd=pro.getProperty("password");
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


    }



    //关闭资源
    public static void close(Connection conn,ResultSet rs,PreparedStatement ps){
        try {
            if(ps!=null){
                ps.close();
            }
            if(rs!=null){
                rs.close();
            }
            if(conn!=null){
                conn.close();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }   
    }


    //增删改
    public static int update(String sql,Object[] Objects){
        int num=0;
        try {
            conn=getConnection();
            ps= conn.prepareStatement(sql);

            if(Objects!=null&& Objects.length>0){
                for (int i = 0; i < Objects.length; i++) {
                    ps.setObject((i+1),Objects[i]);
                }
                num=ps.executeUpdate();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        finally{
            close(conn, null, ps);
        }
        return num;
    }

    //查
    public static ResultSet getSelectAll(String sql,Object[] Objects){
        try {
            ps=getConnection().prepareStatement(sql);
            if(Objects!=null&& Objects.length>0){
                for (int i = 0; i < Objects.length; i++) {
                    ps.setObject((i+1),Objects[i]);
                }
            }
            rs=ps.executeQuery();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return rs;
    }
}

2.DeptDao.java

package com.bdqn.dao;
/**
 * 
 * @author WXY
 * @since 1.0
 *   java连接数据库实现表的增删改查
 *   接口类
 */
import java.util.List;
import com.bdqn.entity.Dept;

public interface DeptDao {
    //增
    int insertDept(Dept dept); 
    //通过id删除
    int delDept(int sid);
    //改
    int updateDept(Dept dept);
    //查全部
    List<Dept> selectDeptAll();
    //通过id查某个
    List<Dept> selectDept(int sid );
}

impl包

1.DeptImpl.java

package com.bdqn.dao.impl;
/**
 * 
 * @author WXY
 * @since 1.0
 *   java连接数据库实现表的增删改查
 *   工具类 --实现接口类的所有方法
 */
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.bdqn.dao.BaseDao;
import com.bdqn.dao.DeptDao;
import com.bdqn.entity.Dept;

//extends继承BaseDao类并且implements实现DeptDao接口
public class DeptImpl extends BaseDao implements DeptDao {
    //增
    @Override
    public int insertDept(Dept dept) {
        int num=0;
        String insert="insert into stu values(?,?,?,?,?)";
        Object[] Objects={dept.getSid(),dept.getSname(),dept.getSsex(),dept.getSage(),dept.getShobby()};
        num=BaseDao.update(insert, Objects);
        return num;
    }
    //删
    @Override
    public int delDept(int sid) {
        int num=0;
        String del="delete from stu where sid=?";
        Object[] Objects={sid};
        num=BaseDao.update(del, Objects);
        return num;
    }
    //改
    @Override
    public int updateDept(Dept dept) {
        int num=0;
        String upd="update stu set sname=? where sid=?";
        Object[] Objects={dept.getSname(),dept.getSid()};
        num=BaseDao.update(upd, Objects);
        return num;
    }
    //查看所有
    @Override
    public List<Dept> selectDeptAll() {

        String sql="select *from stu";
        ResultSet rs=this.getSelectAll(sql,null);
        List<Dept> list=new ArrayList<Dept>();

        try {
            while(rs.next()){
                Dept dept=new Dept(rs.getInt("sid"),rs.getString("sname"),rs.getInt("ssex"),rs.getInt("sage"),rs.getString("shobby"));
                list.add(dept);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }

    //查看某个
    @Override
    public List<Dept> selectDept(int sid) {
        String sql="select *from stu where sid=?";
        Object[]Objects={sid};
        ResultSet rs=this.getSelectAll(sql,Objects);
        List<Dept> list=new ArrayList<Dept>();
        try {
            while(rs.next()){
                Dept dept=new Dept(rs.getInt("sid"),rs.getString("sname"),rs.getInt("ssex"),rs.getInt("sage"),rs.getString("shobby"));
                list.add(dept);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
}

entity包

1、Dept.java

package com.bdqn.entity;
/**
 * 
 * @author WXY
 * @since 1.0
 *   java连接数据库实现表的增删改查
 *   实体类--对象
 */
public class Dept {
    private int sid;
    private String sname;
    private int ssex;
    private int sage;
    private String shobby;
    public int getSid() {
        return sid;
    }
    public void setSid(int sid) {
        this.sid = sid;
    }
    public String getSname() {
        return sname;
    }
    public void setSname(String sname) {
        this.sname = sname;
    }
    public int getSsex() {
        return ssex;
    }
    public void setSsex(int ssex) {
        this.ssex = ssex;
    }
    public int getSage() {
        return sage;
    }
    public void setSage(int sage) {
        this.sage = sage;
    }
    public String getShobby() {
        return shobby;
    }
    public void setShobby(String shobby) {
        this.shobby = shobby;
    }
    public Dept() {
        super();
    }
    public Dept(int sid, String sname, int ssex, int sage, String shobby) {
        this.sid = sid;
        this.sname = sname;
        this.ssex = ssex;
        this.sage = sage;
        this.shobby = shobby;
    }
    @Override
    public String toString() {
        return  sid + "\t" + sname + "\t" + ssex
                + "\t" + sage + "\t" + shobby;
    }
}

utils包

1.Contants.java

package com.bdqn.utils;
/**
*
* @author WXY
* @since 1.0
* java连接数据库实现表的增删改查
* 服务类
*/
public class Contants {
public static String driver;
public static String url;
public static String user;
public static String pwd;
}