// SQL数据库操作示例：
import sqlite3;


// 连接数据库：
conn = sqlite3.connect(‘test.db‘);


//建立新的数据表：

conn.executescript(‘‘‘DROP TABLE IF EXISTS students;
       CREATE TABLE students
       (id INTEGER PRIMARY KEY AUTOINCREMENT,
       name TEXT NOT NULL);‘‘‘)

                   
//插入学生信息：
students = [‘Paul‘,‘Tom‘,‘Tracy‘,‘Lily‘]
for name in students:
    query = "INSERT INTO students (name) VALUES (‘%s‘)" % (name)
    conn.executescript(query);


//检视已有的学生信息：
cursor = conn.execute("SELECT id, name from students")
print(‘IDName‘)
for row in cursor:
    print(‘{0}{1}‘.format(row[0], row[1]))

conn.close();

conn = sqlite3.connect(‘test.db‘)

// 插入包含注入代码的信息
name = "Robert‘);DROP TABLE students;--"
query = "INSERT INTO students (name) VALUES (‘%s‘)" % (name)

conn.executescript(query)

// 检视已有的学生信息
cursor = conn.execute("SELECT id, name from students")
print(‘IDName‘)
for row in cursor:
    print(‘{0}{1}‘.format(row[0], row[1]))

conn.close();

/** 最后执行的sql语句为：
 *  
 * "INSERT INTO students (name) VALUES (‘Robert‘);DROP TABLE students;--"
 *
 * DROP TABLE:删除表数据。
 *
 * sql执行完了insert之后，直接执行了DROP TABLE，把表删除了。故而会出现错误提示：
 *     
 *     表单students无法找到！
 *
 */

/** 首先，JDBC 的 Statement，CallableStatement 和 PreparedStatement 接口定义的方法和属性，
 *  可以让你发送 SQL 命令到数据库，并从你的数据库接收数据，
 *  其中CallableStatement被用来执行数据库访问存储过程的时候。
 * 
 * 原文链接：https://blog.csdn.net/weixin_40803477/java/article/details/80234048
 */

// 创建存储过程：
mysql> CREATE DATABASE EMP $$

mysql> DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$

mysql> CREATE PROCEDURE `EMP`.`getEmpName`
    ->    (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
    -> BEGIN
    ->    SELECT first INTO EMP_FIRST
    ->    FROM Employees
    ->    WHERE ID = EMP_ID;
    -> END $$
mysql> DELIMITER ;


// 创建CallableStatement对象，调用存储过程
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import org.apache.commons.dbcp.BasicDataSource;


public class JdbcConnectedPro {
    
    private static Connection connection=null;
    private static CallableStatement callableStatement = null;
    private static String sql = null;
    
    //创建connection对象
    public static Connection getConnection() {
        
        //创建dataSource
        BasicDataSource dataSource = new BasicDataSource();
        
        //加载数据库驱动
        dataSource.setDriverClassName("org.gjt.mm.mysql.Driver");
        
        //设置用户名和密码
        dataSource.setUrl("jdbc:mysql://localhost:3306/emp");
        dataSource.setUsername("root");
        dataSource.setPassword("root");
        
        try {
            connection=dataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
    
    //基于存储过程实例化CallableStatement对象
    public static CallableStatement getStatement() {
        try {
            callableStatement = connection.prepareCall(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return callableStatement;
    }
    
    //关闭资源
    public static void close() {
        if (null != callableStatement) {
            try {
                callableStatement.close();
                if (null != connection) {
                    connection.close();
                }
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
    
    public static void main(String[] args) {
        sql = "{call getEmpName (?, ?)}";
        try {
            getConnection();
            getStatement();
            callableStatement.setInt(1, 1);
            callableStatement.registerOutParameter(2, Types.VARCHAR);
            callableStatement.execute();//执行
            int result = callableStatement.getInt(2);
            System.out.println("返回结果为"+result);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

class Untitled {
    public static void main(String[] args) {
        Collection ct = null;
        PreprareStatement ps = null;
        ResultSet rs = null;
        
        try{
            
            Class.forName(driveClassName);//载入JDBC驱动程序
            cn = DriverManager.getConnerction(url,user,password);
            
            ps = cn.prepareStatement("select sc.* from score,student s where sc.stId = s.stId and s.name = ? ");
            ps.setString(1,studentName);// 1对应第一个问号，即把传入的参数代入到sql中对应的问号。
            
            rs = ps.executeQuery();
            
            //遍历输出结果
            while(rs.next()){
                //getInt():通过索引或者列名来获得查询结果集中的某一列的值。
                system.out.println(rs.getInt("student_name") + " " + rs.getFloat("score");)
            }
            
        }
        catch(Exception e){
            e.printStackTrace();
        }
        finally{
            try(){
                cn.close();
                ps.close();
                rs.close();
            }catch(SQLException e){ 
                e.printStackTrace();
            }
            
        }
    }
}

//注意事项：
　　变量的定义放在try语句块外面
　　要用finally语句来关闭哥哥对象

 public void addAccount(final Account account) throws DAOException {
          final Connection conn=getConnection();
          PreparedStatement pstmt=con.prepareStatment("insert into account value(?,?,?,?,?,?,?,?,?)");
          pstmt.setString(1,account.getUserName());
          pstmt.setInt(2,account.getPassWord());
          pstmt.setString(3,account.getSex());
          pstmt.setString(4,account.getQq());
          ......
          pstmt.execute();
          conn.Close();
       }