MySQL Developing And Programming Blog

JDBC and Connector/J 12 In the two-tier deployment model, commonly called client/server, the client application communicates directly to the database through a JDBC driver. The JDBC API supports both two-tier and three-tier models for database access. The model supports the database being on the same machine as the client application or on a remote machine, with all communication being handled by JDBC. While the two-tier model is effective and has been in use for many years, there are problems with it, including a lack of security for updates occurring on the database, performance issues, and a lack of scalability. Modern systems use a three-tier deployment model, as shown in Figure 2.2. Business Servlet Other business app Client Java Applet Java Application Java JSP JDBC Server MySQL Database Figure 2.2 A three-tier deployment model. As shown in the three-tier model, the client doesn t have direct access to the database. Instead, the client sends all its requests to a middle, or business, tier. This tier is responsible for implementing all business rules relating to the application and the data that is received from both the client and the database. Using a third tier has many advantages, the least of which is the ability of the business tier to handle security issues with the client application. The business tier is able to determine what a client is allowed to request and to filter data as needed when it is returned from the database. Within the Java arena, three-tier models are commonly created using a JSP page communicated to the client via a Web browser. The JSP triggers a servlet on the business, or middle, tier, where rules and logic are applied to the client s request. The middle tier servlet contacts the database, or third, tier either directly or through EJBs.

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Virtualwebstudio tomcat web hosting provider

What Is J DBC? 11 Database Connectivity, is an API developed by Microsoft to allow access to databases. The API and subsequent interface code allow access to a wide range of databases on many platforms using a variety of languages. This all sounds wonderful for a middleware product. Surely we could use ODBC as an interface between Java and MySQL. Why don t we? The answer isn t as simple as not wanting to use a Microsoft product in our development. It is possible to use ODBC from Java using a product called the JDBC-ODBC Bridge, supplied by Sun. This bridge takes Java commands based on the JDBC API and sends them to an installed ODBC driver, which subsequently accesses the database. Any results work through the software in reverse. The bridge was supplied with Java 1.2 and 2.0 as a stopgap for developers who needed quick access to a database from their Java code. At the time, the JDBC specification wasn t mature; there weren t many vendor drivers available that used JDBC, but many were available for ODBC. Now that all major database vendors have pure Java solutions, use of the bridge isn t encouraged. There are drawbacks to using ODBC in the process of accessing a database through Java. The primary drawback is that the code that implements ODBC is based on the C language and uses a large number of generic pointers. A number of problems occur with interfacing Java to C code, not to mention performance issues. It is much better to have a Java solution to database interfacing in order to provide a seamless solution. Modeling Database Applications with JDBC Before we start to look at the specifics of JDBC, let s take a moment and consider how it is used to interface a Java application with MySQL. Figure 2.1 shows a simple two-tier deployment model. Client Java Applet Java Application JDBC Server MySQL Database Figure 2.1 A two-tier deployment model.
Note: If you are looking for high quality webhost to host and run your jsp application check Vision jsp hosting services

JDBC and Connector/J 10 connecting to the database, to executing SQL statements, to returning results. JDBC is middleware, or an intermediary between the Java language and a database. Fundamentally, JDBC is a specification that provides a complete set of interfaces that allows for portable access to an underlying database. The issue of portability is one of the key aspects of JDBC. Can you imagine using a language like Java which provides the absolute best mechanism for writing an application once and executing it on a large number of platforms and then having to change the code when your organization switches from Microsoft SQL Server to MySQL? That wouldn t be a very portable language in the area of database manipulation. Fortunately, JDBC provides the standard API, and individual database vendors produce the drivers necessary to perform the actual interface between your Java application and the database. This means that Oracle, MySQL, Microsoft, and many other database vendors have taken the time to write all of the code behind the scenes. Since all of the vendors are writing to a common API, you can be relatively certain that the idea of write once, execute often and anywhere is still intact. Because most of the vendor JDBC drivers are also written in Java (more on this in the next section), the drivers can be used on different platforms as well. Not only can you change the platform on which your application runs or where the database itself resides, but you can also change the platform where the database executes. In the case of MySQL, the database system executes on most flavors of Unix and Linux, Windows, and the Macintosh platforms. As you know, Java can be used to write different types of executables, such as Applications Applets Servlets Java ServerPages (JSPs) Enterprise JavaBeans (EJBs) All of these different executables are able to use a JDBC driver to access a database and take advantage of the stored data. Throughout this blog, we use a combination of these applications to illustrate using the MySQL JDBC driver to extract data from a database. For the most part, we use the term Java application to refer to any of the executable types we ve listed, with the possible exception of EJBs. What about ODBC? One of the reasons developers thought JDBC stood for Java Database Connectivity relates to the acronym ODBC (used by Microsoft). ODBC, or Open
Note: If you are looking for good and high quality web space to host and run your java application check Vision java hosting services

CHAPTER 2 JDBC and Connector/J In the previous chapter, we discussed how a database can aid in the development of both Web sites and applications. One of the most popular databases is MySQL. Of course, a language is also needed, and our choice for this blog is Java. By itself, Java doesn t have any way of directly accessing a database. To allow us to achieve the necessary interface between Java and a database, the developers at Sun created a specification called JDBC. In this chapter, we take a comprehensive look at the following: The history of JDBC JDBC driver types Standards and how they affect JDBC The JDBC class MySQL s Connector/J driver What Is JDBC? In this section, we provide a brief overview of what JDBC is and how it came about. Although many believe that JDBC is an acronym for Java Database Connectivity, the JDBC documentation itself states that JDBC isn t an acronym but actually a trademarked name (you can find more information about JDBC at Sun s Web site: http://java.sun.com/products/jdbc/). With that said, JDBC is simply an application programming interface (API) for manipulating a database. The manipulation includes everything from 9
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

An Overview of MySQL 8 Monty and David of MySQL AB eventually became aware of the size of the Java developer community wanting to use MySQL, and extended an offer for me to join their team. In June 2002, I did just that, and MM.MySQL became the official JDBC driver for MySQL. It was subsequently renamed Connector/J. What s Next Now you understand the need for using a database in many of the applications written today. In this chapter, we explained why MySQL is a logical choice. Using the Connector/J JDBC driver, all sorts of Java applications can access a database and its data. In the next chapter, we provide a comprehensive overview of the JDBC specification and how it has been implemented in the Connector/J driver.
Note: If you are looking for best quality webspace to host and run your tomcat application check Vision tomcat hosting services

MySQL and JDBC 7 Access from other languages/systems: There are libraries and APIs for connecting to MySQL from Java (the focus of this blog), C/C++, Perl, PHP, ODBC (Microsoft Windows applications), TCL, Eiffel, and Lisp. Because of this, a whole set of tools has appeared surrounding the use of MySQL from these languages and systems. As you can see, MySQL is a flexible and capable RDBMS that has a rich feature set, performs well on the majority of queries, and has a large support base for access from many different languages. this blog focuses on using MySQL with JDBC, which is what we talk about next. MySQL and JDBC Many developers choose to implement software using Sun s Java technology because of the support Java has for standard Internet concepts such as Web sites, e-mail, and networking. This is the very reason I started to investigate using Java with MySQL in 1994. Sun created a standardized interface to databases from Java called Java Database Connectivity (JDBC). Early in 1994, I was interested in connecting a Java application I was about to develop with the then-new MySQL database system using JDBC. At the time, a rudimentary JDBC driver developed by GWE Technologies existed for MySQL. However, it was missing many features that I required for my project. Because many of the features that I needed would have been difficult to implement in the original MySQL driver, I decided to see if I could implement one myself, more as a tutorial than anything else. After a few weeks of work, I had something that met most of my needs. Through correspondence with other Java developers on the MySQL mailing list, I found that others had a need for a JDBC driver to use with MySQL, and that they required many of the features I had just implemented. Not knowing what would happen, I wrote about the driver I had developed and allowed people to use it. From that small project, the JDBC driver known as MM.MySQL was born. Over the years, through many hundreds of e-mails from users around the world, chronicling bugs and interoperability issues with development tools and application servers, MM.MySQL was fixed and tuned and eventually stabilized to become a successful open source project with a life all of its own. Downloaded by developers from around the world on average close to a thousand times a day, it is one of the most popular JDBC drivers, commercial or open source.

Hint: If you are looking for very good and affordable webspace to host and run your tomcat hosting application check Virtualwebstudio tomcat web hosting provider

An Overview of MySQL 6 requirements, and are implemented only when and if the original requirements can be met as much as possible. Over the years, MySQL has evolved into an RDBMS that has the following core features: Portability: MySQL runs on almost every flavor of Unix, as well as Windows and MacOS X. You can obtain binaries or source code for the MySQL server as well as the tools that access it. More ports of the software become available every day. It is almost a given that MySQL will run on whatever operating system you have available. Speed: Using techniques such as efficient indexing mechanisms, in- memory temporary tables, and highly optimized join algorithms, MySQL executes most queries much faster than most other database systems. Scalability: Because of its modularity and its flexibility in configuration, MySQL can run in systems varying in size from embedded systems to large multiprocessor Unix servers hosting databases with tens of millions of records. This scalability also allows you to run a copy of MySQL on a developer-class machine, and later use the same database system on a larger machine in production. Because it is multithreaded, MySQL efficiently utilizes resources for multiple users, compared to other database servers that start full-fledged processes for each user. It is not uncommon to hear of MySQL installations supporting thousands of concurrent users. Flexibility: MySQL lets you choose the table types you need to meet your software s requirements, ranging from in-memory heap tables, fast on-disk MyISAM tables, merge tables that group together other sets of tables to form larger virtual tables, and transaction-safe tables such as InnoDB. MySQL is also very tunable and includes many parameters that can be changed to increase performance for a given solution. However, MySQL comes with sensible defaults for these parameters, and many users never have to tune MySQL to reach a performance they are happy with. Ease of use: MySQL is easy to install and administer. While other database systems require special knowledge and training, not to mention special operating system configurations, MySQL can be installed in less than 10 minutes if you ve done it before. Even if you are a newcomer, you should be able to install MySQL in under an hour. Once it s installed, MySQL requires little maintenance and administration other than adding or changing user permissions and creating or removing databases. Fine-grained security model: You can restrict users rights from an entire database down to the column level based on login name, password, and the hostname that users are connecting from. This allows you to create secure systems by partitioning responsibilities and capabilities of different users and applications to prevent unauthorized modification or retrieval of data.
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

Why Choose MySQL? 5 Ad Hoc Queries It is likely that your software will need to retrieve stored data using arbitrary parameters, otherwise known as ad hoc queries. This becomes difficult with flat files because they are not self-describing, and every file layout is different. You also need to consider how you are going to read the data for these queries from your persistent storage mechanism. Many RDBMSs use SQL (Structured Query Language) for manipulating data. SQL is a declarative language in that you declare what data you want, not the procedure for how to get it. SQL is also an accepted and widely used standard, so a large set of tools are available (JDBC and Enterprise Java Beans, among them) to help you work with it. After outlining all of the benefits of an RDBMS, I hope you are ready to consider using one for your software projects. The next question to ask is Why choose MySQL? Why Choose MySQL? As was the case with many other open source projects, MySQL was first created by someone who needed a better tool to get a specific job done. Monty Widenius and David Axmark started out with another open source project (MSQL), but found that it lacked some features that they needed. They decided to develop their own database system that met their specific requirements. They started building MySQL by using some low-level database storage code they had already developed for other projects and layered a multithreaded server, SQL parser, and client-server protocol on top. They also structured the API for MySQL to appear very similar to MSQL in order to make it easier for developers to port their MSQL-based software to MySQL. MySQL was eventually released in source-code form, under a proprietary license. Eventually, this license was changed to the GNU General Public License (GPL), which in most cases allows the software to be used without license cost. However, in certain situations you must purchase a commercial license. The exact terms of the license are available in the documentation that ships with MySQL or on the Web at www.mysql.com. Commercial support is also available for those who need it from MySQL-AB, the company that was created by Monty and David to support the continued development of the MySQL software. The requirements that Monty and David originally had for MySQL were that it be as fast as possible, while still being stable, simple to use, and able to meet the needs of the majority of database developers. Even today, feature requests for future MySQL development are weighed carefully against these original

Hint: If you are looking for very good and affordable webspace to host and run your j2ee hosting application check Virtualwebstudio j2ee web hosting services

An Overview of MySQL 4 monthly statement. Without isolation, the monthly statement might show the withdrawal from the checking account but not the deposit into the savings account. This discrepancy would make it impossible for Jack or the bank to balance their books. Durability: Once completed, a transaction s changes are never lost through system or hardware crashes. If Jill has paid for $50 worth of groceries with her debit card at the grocery store and the transaction succeeds, even if the database software crashes immediately after the transaction competes, it won t forget that her checking account balance is $50 lower. Until recently, MySQL did not comply with all components of the ACID test. However, with the new BDB and InnoDB table types (supported in MySQL 3.23 and MySQL 4.0), MySQL can now pass the ACID test. Not all software requires the robustness (or the associated overhead) of transaction semantics. MySQL is one of the only databases that enable you to decide what level of robustness you need on a table-by-table basis. This becomes important when you are trying to maximize performance, especially when much of the data is read-only (such as in a product catalog). Searching, Modifying, and Analyzing Data Any time you store a significant amount of data with your software, your users want to search, modify, and analyze the data you have stored. If you are using flat files, you most likely have to develop this functionality yourself. As your data stored in flat files takes up more and more space, it takes longer and longer to search. A common solution to this problem is to create an index for your data. Indexes are basically shortcuts to finding a particular piece of data, usually using some sort of key. If you need to develop indexing functionality yourself, you have to learn about data structures, such as hashes and B- trees, and how to store these indexes alongside your data. In addition, you must learn how to implement the index in your software. If you use an RDBMS, you can tell the database system what data you think people will search on, and it does all of the fancy indexing for you. Users of your software also want to retrieve, modify, and analyze the data you have stored. They expect that your system knows how to compute such values as sums, averages, minimums, and maximums to be used for updating related data or analyzing existing data. They expect that your software will be able to sort the data or group the data by similar attributes. All of this functionality requires you to implement numerous functions and algorithms. If you use an RDMBS, all of these features are built in.
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services

Why Use an RDBMS? 3 every time the location or layout of the files changes, you are required to change your software. Once the datasets your software is storing become numerous or large, the storage management process becomes cumbersome. Using a database system gives you storage transparency. Your software does not care where and how the data is stored. The data can even be stored on some other computer and accessed via networking protocols. Transactions When you have more than one user accessing and changing your data, you want to make these changes transactional. Transactions group operations on your data into units of work that meet the ACID test. The ACID test concept is best illustrated with a commonly used example from the banking industry. Jack and Jill share a joint checking account with a balance of $1000. They are both performing various operations, such as deposits, withdrawals, and transfers, on the account. Let s see how the four aspects of the ACID test come into play: Atomicity: All changes made during a transaction are made successfully, or in the case of failure, none are made. If any operation fails during the transaction, then the entire transaction is rolled back, leaving your data in the state it was before the transaction was started. For example, suppose Jack is making a transfer of $500 from his checking account to a savings account. Sometime between the withdrawal of the $500 from the checking account and the deposit of $500 to the savings account, the software running the banking system crashes. Jack s $500 has disappeared! With atomicity, either the entire transfer would have happened, or none of it would have happened, leaving Jack a much happier customer than he is now. Consistency: All operations transform the database from one consistent state to another consistent state. Consistency is defined by how the database schema is designed and whether integrity constraints such as foreign keys are used. The database management system is responsible for ensuring that transactions do not violate the database schema or integrity constraints. For example, the bank s database developers have declared in the database schema that the balance of an account cannot be empty, or null. If any transaction attempts to set the balance to an empty value, the transaction will be aborted and any changes rolled back. Isolation: A transaction s changes are not made visible to other transactions until they are committed under the atomicity rule described earlier. This is best demonstrated by what happens when month-end reports are generated. Let s say that Jack is performing the transfer transaction outlined in the atomicity example, and at the same time you are generating his
Note: If you are looking for cheap and quality provider to host and run your java application check Astra java hosting services