public static void main(String[] args) throws Exception {
try (Connection conn = DBUtil.getConnection(DBType.HSQLDB);
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("SELECT stateId, stateName FROM states"); ) {
States.displayData(rs);
rs.last();
System.out.println("Number of rows: " + rs.getRow());
rs.first();
System.out.println("The first state is " + rs.getString("stateName"));
rs.last();
System.out.println("The last state is " + rs.getString("stateName"));
rs.absolute(10);
System.out.println("The 10th state is " + rs.getString("stateName"));
} catch (SQLException e) {
System.err.println(e);
}
}
public static List<Sucursal> buscarSucursales() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Sucursal> sucursales = new ArrayList();
cs = connection.prepareCall("{ call listaSucursal() }");
rs = cs.executeQuery();
while (rs.next()) {
Sucursal suc = new Sucursal(rs.getInt("idSucursal"), rs.getString("nombreSucursal"));
sucursales.add(suc);
}
return sucursales;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static List<Departamento> buscarDepartamentos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Departamento> departamentos = new ArrayList();
cs = connection.prepareCall("{ call listaDepartamento() }");
rs = cs.executeQuery();
while (rs.next()) {
Departamento dpo =
new Departamento(rs.getInt("idDepartamento"), rs.getString("nombreDepartamento"));
departamentos.add(dpo);
}
return departamentos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
/** @param args */
private static void registerStudents() throws Exception {
Scanner keyboard = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Properties config = DBUtil.getConfigProperties();
Connection conn = DBUtil.connectToDatabase(config, "cloudcoder.db");
List<Course> courses =
DBUtil.getAllModelObjects(
conn,
Course.SCHEMA,
new IFactory<Course>() {
@Override
public Course create() {
return new Course();
}
});
Course c =
ConfigurationUtil.choose(
keyboard, "For which course would you like to register students?", courses);
// TODO: look up the term for each course
String filename =
ConfigurationUtil.ask(
keyboard,
"Enter the name of the file containing a tab-separated list student registration entries in this format: \n"
+ "username\tfirstname\tlastname\temail\tpassword\tsection\n"
+ "Usernames in the datbase will be re-used, but the names/email/password will not be updated,"
+ "and users will not be registered for a course if they are already registered");
int num =
ConfigurationUtil.registerStudentsForCourseId(
new FileInputStream(filename), c.getId(), conn);
System.out.println("Registered " + num + " students for " + c.getName());
}
public static List<Pago> buscarPagos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Pago> pagos = new ArrayList();
cs = connection.prepareCall("{ call listaPago() }");
rs = cs.executeQuery();
while (rs.next()) {
Pago pag = new Pago(rs.getInt("idMetodoPago"), rs.getString("nombreMetodoPago"));
pagos.add(pag);
}
return pagos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public SiteInternal find(URL siteURL) {
SiteInternal site = null;
Statement st = null;
ResultSet rs = null;
try {
Connection c = dbUtil.getConnection();
st = c.createStatement();
rs =
st.executeQuery(
"select * from jspider_site where host = '"
+ siteURL.getHost()
+ "' and port = "
+ siteURL.getPort());
if (rs.next()) {
site = createSiteFromRecord(rs);
} else {
return null;
}
} catch (SQLException e) {
log.error("SQLException", e);
} finally {
dbUtil.safeClose(rs, log);
dbUtil.safeClose(st, log);
}
return site;
}
public static List<Usuario> buscarUsuarios() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Usuario> usuas = new ArrayList();
cs = connection.prepareCall("{ call cajeroReporte() }");
rs = cs.executeQuery();
while (rs.next()) {
Usuario usa =
new Usuario(
rs.getString("nombreUsuario"),
rs.getString("apellidoPaterno"),
rs.getInt("idUsuario"));
usuas.add(usa);
}
return usuas;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
@Before
public void setUp() throws Exception {
DBUtil.reDeployDB();
WebBeanConstructor con = new WebBeanConstructor();
deployer = new Deployer("deployerXml/mevoco/TestMevoco.xml", con);
deployer.addSpringConfig("mevocoRelated.xml");
deployer.load();
loader = deployer.getComponentLoader();
bus = loader.getComponent(CloudBus.class);
dbf = loader.getComponent(DatabaseFacade.class);
config = loader.getComponent(LocalStorageSimulatorConfig.class);
fconfig = loader.getComponent(FlatNetworkServiceSimulatorConfig.class);
Capacity c = new Capacity();
c.total = totalSize;
c.avail = totalSize;
config.capacityMap.put("host1", c);
config.capacityMap.put("host2", c);
deployer.build();
api = deployer.getApi();
session = api.loginAsAdmin();
}
/**
* Test method for {@link
* org.transferobject.core.DBUtil#addStudent(org.transferobject.student.StudentVO)}.
*/
@Test
public void testAddStudent() {
StudentVO student = new StudentVO();
student.setRegistrationNumber("STDREG00001");
student.setStudentName("Jeremy Clarkson");
assertTrue("Student added", DBUtil.addStudent(student));
}
public static int addStudy(Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO study (name,description,creatorEmail, dateCreated, "
+ "question, imageURL, requestedParticipants, numOfParticipants, status) "
+ "VALUES (?,?,?,?,?,?,?,?,?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getCreatorEmail());
ps.setTimestamp(4, study.getDateCreated());
ps.setString(5, study.getQuestion());
ps.setString(6, study.getImageURL());
ps.setInt(7, study.getRequestedParticipants());
ps.setInt(8, study.getNumOfParticipants());
ps.setString(9, study.getStatus());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
// 使用回调机制，复用代码
public ResultSet execute(StatementCallback callback) {
Connection connection = DBUtil.getConnection();
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
connection.setAutoCommit(false);
// 调用回调函数
resultSet = callback.doInStatement(statement);
connection.commit();
} catch (SQLException e) {
log.error(e);
try {
connection.rollback();
} catch (SQLException e1) {
log.error(e);
}
} finally {
try {
statement.close();
connection.close();
} catch (SQLException e) {
log.error(e);
}
}
return resultSet;
}
public static int update(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE customer SET "
+ "first_name = ?, "
+ "last_name = ?, "
+ "phone_number = ?, "
+ "address = ?, "
+ "city = ?, "
+ "state = ?, "
+ "zip_code = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static int insertarVenta(Venta v) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
Venta ven = null;
try {
cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }");
cs.setInt(1, v.getUsuarioVenta().getId());
cs.setDouble(2, v.getSubtotal());
cs.setInt(3, v.getPagoVenta().getIdPago());
cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal());
rs = cs.executeQuery();
while (rs.next()) {
ven = new Venta(rs.getInt("idVenta"));
}
return ven.getIdVenta();
} catch (Exception ex) {
ex.printStackTrace();
return 0;
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int updateStudy(String code, Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE study SET "
+ "name = ?, description = ?, question = ? , imageURL = ?, requestedParticipants = ?,"
+ "numOfParticipants = ?, status = ?"
+ "WHERE code = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getQuestion());
ps.setString(4, study.getImageURL());
ps.setInt(5, study.getRequestedParticipants());
ps.setInt(6, study.getNumOfParticipants());
ps.setString(7, study.getStatus());
ps.setString(8, code);
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
/**
* 根据学号和密码查找学生
*
* @param studentNo 学号
* @param password 密码
* @return Studen
*/
public static Student findStudent(String studentNo, String password) {
Student student = null;
try {
Connection conn = DBUtil.createConnection();
Statement stat = conn.createStatement();
ResultSet rs =
stat.executeQuery(
"SELECT * FROM STUDENT WHERE student_no=\""
+ studentNo
+ "\" AND password=\""
+ password
+ "\"");
while (rs.next()) {
student = new Student();
student.setId(rs.getInt("id"));
student.setPassword(rs.getString("password"));
student.setName(rs.getString("name"));
student.setStudentNO("student_no");
}
rs.close();
stat.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
return student;
}
public static int insert(User user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO customer (first_name, last_name, phone_number, address, city, state, zip_code) "
+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getPhone());
ps.setString(4, user.getAddress());
ps.setString(5, user.getCity());
ps.setString(6, user.getState());
ps.setString(7, user.getZipCode());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public SiteInternal[] findAll() {
ArrayList al = new ArrayList();
Statement st = null;
ResultSet rs = null;
try {
Connection c = dbUtil.getConnection();
st = c.createStatement();
rs = st.executeQuery("select * from jspider_site");
while (rs.next()) {
al.add(createSiteFromRecord(rs));
}
} catch (SQLException e) {
log.error("SQLException", e);
} finally {
dbUtil.safeClose(rs, log);
dbUtil.safeClose(st, log);
}
return (SiteInternal[]) al.toArray(new SiteInternal[al.size()]);
}
public static void main(String[] args) {
TBlueprint tblueprint = new TBlueprint();
tblueprint.setSnBlueprint("AAAAAAAAA");
InspectInterceptor interceptor = new InspectInterceptor("usky");
CurrentUser cu = new CurrentUser();
cu.setAccountName("usky");
// HibernateSessionFactory.rebuildSessionFactory();
DBUtil.save(tblueprint, interceptor);
}
public void getAllTixing() {
List<String> tixingList = new ArrayList<String>();
dbUtil.getAllTixingName(tixingList);
for (String s : tixingList) {
int id = Integer.valueOf(s.substring(0, s.indexOf("+")));
String name = s.substring(s.indexOf("+") + 1, s.length());
tixingNameMap.put(id, name);
ids[id] = 1;
}
}
private String getMessageFromDB() {
String msg = null;
try {
DBUtil dbUtil = DBUtil.getInstance();
Connection conn = dbUtil.getConnection();
PreparedStatement stmt = conn.prepareStatement("select msg from hello");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
msg = rs.getString("msg");
}
} catch (Exception ex) {
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
ex.printStackTrace(pw);
msg = sw.toString();
}
return msg;
}
public static void main(String[] args) throws Exception {
Variables.applicationHome = System.getProperty("user.dir");
Logger.out = org.apache.log4j.Logger.getLogger("");
PropertyConfigurator.configure(
Variables.applicationHome + "\\WEB-INF\\src\\" + "ApplicationResources.properties");
Logger.out.debug("here");
DBUtil.currentSession();
System.out.println(mappings.size());
}
public static ArrayList<Study> getOpenAndNotParticipated(String emailAddress) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query =
"SELECT * FROM study WHERE NOT EXISTS(SELECT * FROM answer WHERE study.code = answer.code AND email = ?)"
+ "AND status = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, emailAddress);
ps.setString(2, "open");
rs = ps.executeQuery();
Study study = null;
ArrayList<Study> studies = new ArrayList<Study>();
while (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
studies.add(study);
}
return studies;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
// =============================================================
public static boolean UserExists(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT username FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
private void IsGood() {
String id = stuff_id.getText().trim();
String name = stuff_name.getText().trim();
String company = stuff_company.getText().trim();
String people = stuff_people.getText().trim();
String check = check_people.getText().trim();
String unit = stuff_unit.getText().trim();
String value = stuff_value.getText().trim();
String spec = stuff_spec.getText().trim();
String color = stuff_color.getText().trim();
String place = stuff_place.getText().trim();
String date = stock_date.getText().trim();
String text = stuff_text.getText().trim();
String str[] = {id, name, company, people, check, unit, value, spec, color, place, date, text};
for (int i = 0; i < str.length; i++) {
if (str[i].isEmpty()) {
javax.swing.JOptionPane.showMessageDialog(
null, "必填项，请务必输入！", "必须输入", javax.swing.JOptionPane.ERROR_MESSAGE);
return;
}
}
if (!CheckValue(value)) {
javax.swing.JOptionPane.showMessageDialog(
null, "请检验你输入的数量是不是数字，请重新输入！", "重新输入", javax.swing.JOptionPane.ERROR_MESSAGE);
stuff_value.setText("");
stuff_value.requestFocus();
return;
}
sql =
"select stuff_ID,stuff_name,stuff_value from stuff_in where stuff_ID="
+ id
+ " and stuff_name="
+ name
+ " and stuff_value =>"
+ value;
if (!DBUtil.isExist(sql)) {
javax.swing.JOptionPane.showMessageDialog(
null,
"请检验你输入的项编号、产品名以有数量有没有超过以前输入的数量，请重新输入！",
"重新输入",
javax.swing.JOptionPane.ERROR_MESSAGE);
return;
}
sql =
"'" + id + "','" + name + "','" + company + "','" + people + "','" + check + "','" + unit
+ "','" + value + "','" + spec + "','" + color + "','" + place + "','" + date + "','"
+ text + "'";
sql = "insert into yield_draw values(" + sql + ")";
USeDB.UpdateDB(sql);
sql = "";
clean();
}
@Before
public void setUp() throws Exception {
DBUtil.reDeployDB();
WebBeanConstructor con = new WebBeanConstructor();
deployer = new Deployer("deployerXml/kvm/TestStartVmOnTargetHost.xml", con);
deployer.addSpringConfig("KVMRelated.xml");
deployer.build();
api = deployer.getApi();
loader = deployer.getComponentLoader();
bus = loader.getComponent(CloudBus.class);
dbf = loader.getComponent(DatabaseFacade.class);
config = loader.getComponent(SftpBackupStorageSimulatorConfig.class);
session = api.loginAsAdmin();
}
public static void delete(User1 User1) {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
EntityTransaction trans = em.getTransaction();
trans.begin();
try {
em.remove(em.merge(User1));
trans.commit();
} catch (Exception e) {
System.out.println(e);
trans.rollback();
} finally {
em.close();
}
}
public static User1 selectUser1(String email) {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
String qString = "SELECT u FROM User1 u " + "WHERE u.email = :email";
TypedQuery<User1> q = em.createQuery(qString, User1.class);
q.setParameter("email", email);
try {
User1 User1 = q.getSingleResult();
return User1;
} catch (NoResultException e) {
return null;
} finally {
em.close();
}
}
public static void insert(Lineitem lineitem) {
EntityManager em = DBUtil.getEmFactory().createEntityManager();
EntityTransaction trans = em.getTransaction();
trans.begin();
try {
em.persist(lineitem);
trans.commit();
} catch (Exception e) {
System.out.println(e);
trans.rollback();
} finally {
em.close();
}
}
// -----
public static int insert(UserInfo user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
// This method adds a new record to the Users table in the database
String query =
"INSERT INTO user_info (firstName, lastName, address, affiliation, "
+ "memDur, username, password, cardType, cardNumber, email) "
+ "VALUES (?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getAddress());
ps.setString(4, user.getAffiliation());
ps.setString(5, user.getMemDur());
ps.setString(6, user.getUsername());
ps.setString(7, user.getPassword());
ps.setString(8, user.getCardType());
ps.setString(9, user.getCardNumber());
ps.setString(10, user.getEmail());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static UserInfo selectUser(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
UserInfo user = null;
if (rs.next()) {
user = new UserInfo();
user.setFirstName(rs.getString("firstName"));
user.setLastName(rs.getString("lastName"));
user.setAddress(rs.getString("address"));
user.setAffiliation(rs.getString("affiliation"));
user.setMemDur(rs.getString("memDur"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCardType(rs.getString("cardType"));
user.setCardNumber(rs.getString("cardNumber"));
user.setCardNumber(rs.getString("email"));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
