jdbc

package cn.itcast.jdbc;
/*
jdbc快速入门
 */


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

public class jdbcDemo01 {
    public static void main(String[] args) throws Exception {
//1.导入驱动jar包
//2.注册驱动
        Class.forName("com.mysql.jdbc.Driver");
//3获取数据库连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
//4定义sql语句
        String sql = "update emp set salary = 1000.0 where id = 1001";
//5.获取执行sql的对象 statement
        Statement stmt = connection.createStatement();
//6.执行sql
        int count = stmt.executeUpdate(sql);
//7.处理结果
        System.out.println(count);
//8.释放资源
        stmt.close();
        connection.close();



    }
}

inser into

package cn.itcast.jdbc;
/*
添加记录
 */

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

public class jdbcDemo02 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        //1,注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2,定义sql
            String sql = "insert into account(id,balance) values(3,600) ";

            try {
                //3,获取数据库连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
                //4,获取sql执行对象
                stmt = conn.createStatement();
               //5.执行sql
                int i = stmt.executeUpdate(sql);
                if (i > 0) {
                    System.out.println("添加成功");
                } else {
                    System.out.println("添加失败");
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //释放资源 避免空指针异常
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

    }
}

修改

package cn.itcast.jdbc;
/*
修改记录
 */

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

public class jdbcDemo03 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        try {
            //1注册驱动
            Class.forName("com.mysql.jdbc.Driver");

            try {
                //获取数据库连接对象
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3", "root", "root");
                //定义sql语句
                String sql = "Update account set balance = 6666 where id = 2";
                //获取执行sql的对象
                stmt = conn.createStatement();
                //执行sql
                int i = stmt.executeUpdate(sql);
                if (i > 0) {
                    System.out.println("修改成功");
                } else {
                    System.out.println("修改失败");
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

删除记录

package cn.itcast.jdbc;
/*
删除一条记录
 */

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

public class jdbcDemo04 {
    public static void main(String[] args) {
        Statement stmt = null;
        Connection conn = null;
        //1，注册驱动，导入驱动字节码
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //2,创建数据库连接对象
            try {
                conn = DriverManager.getConnection("jdbc:mysql://localhost/db3", "root", "root");
                //3,定义sql语句
                String sql = "delete from account where id = 3";
                //创建执行sql的对象
                stmt = conn.createStatement();
                //执行sql
                int i = stmt.executeUpdate(sql);
                System.out.println(i);
            } catch (SQLException e) {
                e.printStackTrace();
            }

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if(stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

 查询练习

package cn.itcast.domain;


import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class jdbcDemo07 {
    public static void main(String[] args) {
        List<Emp> emps = findAll();
        for (Emp emp : emps) {
            System.out.println(emp);
        }
        System.out.println(emps.size());

    }

    public static List<Emp> findAll() {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        List<Emp> list = null;
        //注册驱动
        try {
            Class.forName("com.mysql.jdbc.Driver");

            //获取数据库连接
            try {
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db3","root","root");
                //获取执行sql的对象
                stmt = conn.createStatement();
                String sql = "select * from emp";
                //执行sql
                rs = stmt.executeQuery(sql);
                Emp emp = null;
                list = new ArrayList<Emp>();
                //遍历结果集对象
                while (rs.next()) {
                    int id = rs.getInt("id");
                    String ename = rs.getString("ename");
                    int job_id = rs.getInt("job_id");
                    int mgr = rs.getInt("mgr");
                    Date joindate = rs.getDate("joindate");
                    double salary = rs.getDouble("salary");
                    double bonus = rs.getDouble("bonus");
                    int dept_id = rs.getInt("dept_id");
                    emp = new Emp();
                    emp.setId(id);
                    emp.setEname(ename);
                    emp.setJob_id(job_id);
                    emp.setMgr(mgr);
                    emp.setJoindate(joindate);
                    emp.setSalary(salary);
                    emp.setBonus(bonus);
                    emp.setDept_id(dept_id);
                    list.add(emp);

                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        return list;
    }
}

 jdbc工具类抽取

package cn.itcast.util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * jdbc工具类
 */


public class JDBCutils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取只需要一次既可以拿到值，可以使用静态代码块
     */
    static {
        //读取资源文件，获取值

        try {
            //创建集合类对象
            Properties prop = new Properties();
            //获取src目录下的文件的方式--》ClassLoader 类加载器
            ClassLoader classLoader = JDBCutils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            System.out.println(path);

            //加载配置文件
            prop.load(new FileReader(path));
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            driver = prop.getProperty("driver");
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     *
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放资源
     *
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放资源
     *
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

---

利用jdb工具类c完成模拟登陆练习

jdbc工具类如下：

package cn.itcast.util;

import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.Properties;

/**
 * jdbc工具类
 */


public class JDBCutils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取只需要一次既可以拿到值，可以使用静态代码块
     */
    static {
        //读取资源文件，获取值

        try {
            //创建集合类对象
            Properties prop = new Properties();
            //获取src目录下的文件的方式--》ClassLoader 类加载器
            ClassLoader classLoader = JDBCutils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath();
            System.out.println(path);

            //加载配置文件
            prop.load(new FileReader(path));
            url = prop.getProperty("url");
            user = prop.getProperty("user");
            password = prop.getProperty("password");
            driver = prop.getProperty("driver");
            try {
                Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接对象
     *
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放资源
     *
     * @param stmt
     * @param conn
     */
    public static void close(Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放资源
     *
     * @param stmt
     * @param conn
     */
    public static void close(ResultSet rs, Statement stmt, Connection conn) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

期间利用debug工具判断出错误地点

package cn.itcast.jdbc;
/*
通过键盘录入用户名和密码。判断是否登录成功
 */

import cn.itcast.util.JDBCutils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class jdbcDemo09 {
    public static void main(String[] args) throws SQLException {
        Scanner sc = new Scanner(System.in);
        System.out.println("请输入用户名");
        String inputusername = sc.nextLine();
        System.out.println("请输入密码");
        String inputpassword = sc.nextLine();
        boolean flag = new jdbcDemo09().login(inputusername, inputpassword);
        if (flag ) {
            System.out.println("登录成功");
        } else {
            System.out.println("账号或密码错误请重新再试");
        }
    }

    /**
     * 登录方法
     */
    public boolean login(String username, String password) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        if (username == null || password == null) {
            return false;
        }
        try {
            conn = JDBCutils.getConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from user ");
            while (rs.next()) {
                String username1 = rs.getString("username");
                String password1 = rs.getString("password");
                if (username.equals(username1)  && password .equals(password1) ) {
                    return true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCutils.close(rs, stmt, conn);
        }

        return false;
    }
}

配置文件

url=jdbc:mysql:///db4
user=root
password=root
driver=com.mysql.jdbc.Driver

---