java操作数据库

依赖

 <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
</dependency>

添加

package src.com.bjpowernode.jdbc;

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

public class JDBCinsert {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //1.注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2.获取数据库连接
            String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false&characterEncoding=utf8";
            String user = "root";
            String password ="123456789";
            conn = DriverManager.getConnection(url, user, password);
            //3.定义SQL语句框架
            String sql = "insert into yellpic(picurl,pic,picdes,aliurl,sta) values(?,?,?,?,?)";
            //4.进行SQL语句的预编译
            ps = conn.prepareStatement(sql);
            //5.进行赋值
            ps.setString(1, "lily");
            ps.setString(2, "111");
            ps.setString(3, "111");
            ps.setString(4, "111");
            ps.setString(4, "111");

            //6.执行SQL语句
            int count = ps.executeUpdate();
            System.out.println("插入成功");
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }

            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}

执行修改

package src.com.bjpowernode.jdbc;

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

public class JDBCTest13 {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3366/bjpowernode", "root", "111");
            //开启事务
            conn.setAutoCommit(false);
            //定义SQL语句框架
            String sql = "update emp_bak1 set sal = ? where job = ?";
            //进行SQL语句预编译
            ps = conn.prepareStatement(sql);
            //进行赋值
            ps.setDouble(1, 4000);
            ps.setString(2, "MANAGER");
            //执行SQL语句
            int count = ps.executeUpdate();
            System.out.println(count);
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally{
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}

查找

package src.com.bjpowernode.jdbc;

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

public class JDBCTest13 {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3366/bjpowernode", "root", "111");
            //开启事务
            conn.setAutoCommit(false);
            //定义SQL语句框架
            String sql = "update emp_bak1 set sal = ? where job = ?";
            //进行SQL语句预编译
            ps = conn.prepareStatement(sql);
            //进行赋值
            ps.setDouble(1, 4000);
            ps.setString(2, "MANAGER");
            //执行SQL语句
            int count = ps.executeUpdate();
            System.out.println(count);
            //提交事务
            conn.commit();
        } catch (Exception e) {
            //事务回滚
            try {
                conn.rollback();
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }
        }finally{
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}

删除

package src.com.bjpowernode.jdbc;

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

public class JDBCTest09 {

    /**
     * @param args
     */
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            //注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //获取数据库连接
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3366/bjpowernode", "root", "111");
            //定义SQL语句框架
            String sql = "delete from t_user where id = ?";
            //进行SQL语句的预编译
            ps = conn.prepareStatement(sql);
            //进行赋值
            ps.setInt(1, 1);
            //执行SQL语句
            int count = ps.executeUpdate();
            System.out.println(count);
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally{
            //关闭资源
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

}